Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: Can't figure out this script Excel thing

  1. #1
    Senior Member Webhead's Avatar
    Join Date
    Jan 2014
    Posts
    1,829
    vCash
    500
    Points
    680,071
    Bank
    0
    Total Points
    680,071
    Donate

    Can't figure out this script Excel thing

    Anyone familiar with macros in Excel? Trying to figure out how to copy a row of data on a sheet in one workbook to a sheet in another workbook automatically.

    For example, let's say you have the first workbook. It contains rows of data about various products. Let's say pencils, paper and erasers. Then you have 3 other Excel files... One for pencils, one for paper and one for erasers.

    So on the main one, let's say you start at the beginning and start typing in information about a pencil. Then at the end of the row, you mark it as a "pencil" (maybe with a data validation menu or something?) But by choosing it as a pencil, it then copies the row and appends to the "pencil.xlsx" workbook. Then you go to the next row and start typing in information about paper. At the end, you select "paper" and then that row copies over and appends to "paper.xlsx".

    Does that make sense? Is this too complicated? Or is it easy and I'm just slow? Because I'm having trouble making this work.

    So far I found a script that comes close. It's applied to a button. When the button is pressed, it copies stuff marked as "Yes" to another sheet. But I want it to actually go to a sheet on another file.

    Code:
    Sub SearchForString()
    
        Dim LSearchRow As Integer
        Dim LCopyToRow As Integer
        
        On Error GoTo Err_Execute
        
        'Start search in row 4
        LSearchRow = 4
        
        'Start copying data to row 2 in Sheet2 (row counter variable)
        LCopyToRow = 2
        
        While Len(Range("A" & CStr(LSearchRow)).Value) > 0
            
            'If value in column E = "Yes", copy entire row to Sheet2
            If Range("D" & CStr(LSearchRow)).Value = "Yes" Then
                
                'Select row in Sheet1 to copy
                Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
                Selection.Copy
                
                'Paste row into Sheet2 in next row
                Sheets("Sheet2").Select
                Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
                ActiveSheet.Paste
                
                'Move counter to next row
                LCopyToRow = LCopyToRow + 1
                
                'Go back to Sheet1 to continue searching
                Sheets("Sheet1").Select
                
            End If
            
            LSearchRow = LSearchRow + 1
            
        Wend
        
        'Position on cell A3
        Application.CutCopyMode = False
        Range("A3").Select
        
        MsgBox "All matching data has been copied."
        
        Exit Sub
        
    Err_Execute:
        MsgBox "An error occurred."
        
    End Sub

    And if my words are confusing, then maybe this will clear it up. I'm thinking it would work something like this...
    Last edited by Webhead; 06-09-2014 at 11:01 PM.

  2. #2
    Senior Member CeeBee's Avatar
    Join Date
    Jan 2014
    Posts
    1,677
    vCash
    1792
    Points
    141,465
    Bank
    0
    Total Points
    141,465
    Donate
    I'm not 100% of your actual issue, but I guess it is writing to a different file, not looking for data.
    This should give you a hint:
    Code:
    Function OpenWorkbook(ByVal fileId As String) As Workbook
        Dim fileName As String
        fileName = fileId + ".xlsx"
        Set OpenWorkbook = Workbooks.Open(fileName)
    End Function
    
    Sub CloseWorkbook(ByRef wBook As Workbook)
        wBook.Close (True)
    End Sub
    
    
    Sub Test()
        Dim wb As Workbook
       
        Set wb = OpenWorkbook("Webby")
        
        Set curSheet = ActiveSheet
        Set destSheet = wb.Sheets("Sheet1")
        destSheet.Cells(1,7) = curSheet.Cells(2, 3)
        
        Set destSheet = wb.Sheets("Sheet2")
        destSheet.Cells(2, 5) = "TestData"
        CloseWorkbook wb
    End Sub
    Also as a hint to loop through all the records you can do:
    Code:
    Set ws=ActiveSheet
    lastRow=ws.UsedRange.Rows.Count
    For i=1 to lastRow
    ...
    Next i
    That takes care of dealing with empty cells which would break your While loop
    Last edited by CeeBee; 06-10-2014 at 02:50 PM.

  3. #3
    Senior Member Webhead's Avatar
    Join Date
    Jan 2014
    Posts
    1,829
    vCash
    500
    Points
    680,071
    Bank
    0
    Total Points
    680,071
    Donate
    Man, that is so awesome that you know how to write that. I need to learn how to do this. I'll give your script above a go and see if I can learn from it.

    But yeah, here's what I'm trying to accomplish:

    File 1:
    - User enters data on a row.
    - Once the data is entered, it should then copy that row to another file in the other files next available row.
    - For example, depending on the variable, the row of text will copy to file1, file2 or file3.

    File 2:
    - Data pastes to next available row.

  4. #4
    Senior Member CeeBee's Avatar
    Join Date
    Jan 2014
    Posts
    1,677
    vCash
    1792
    Points
    141,465
    Bank
    0
    Total Points
    141,465
    Donate
    Quote Originally Posted by Webhead View Post
    Man, that is so awesome that you know how to write that. I need to learn how to do this. I'll give your script above a go and see if I can learn from it.

    But yeah, here's what I'm trying to accomplish:

    File 1:
    - User enters data on a row.
    - Once the data is entered, it should then copy that row to another file in the other files next available row.
    - For example, depending on the variable, the row of text will copy to file1, file2 or file3.

    File 2:
    - Data pastes to next available row.
    Properly defining the requirements is key.
    What is "entering data"? New record? Modifying an existing record?
    If a new record is added and it already exists, should it be duplicated?
    If an existing record is changed, should it be deleted from the previous file and added to the new one?
    What happens if a record is deleted?
    What happens if a new record is inserted in the middle?

  5. #5
    Senior Member Webhead's Avatar
    Join Date
    Jan 2014
    Posts
    1,829
    vCash
    500
    Points
    680,071
    Bank
    0
    Total Points
    680,071
    Donate
    Quote Originally Posted by CeeBee View Post
    Properly defining the requirements is key.
    What is "entering data"? New record? Modifying an existing record?
    If a new record is added and it already exists, should it be duplicated?
    If an existing record is changed, should it be deleted from the previous file and added to the new one?
    What happens if a record is deleted?
    What happens if a new record is inserted in the middle?
    Entering data = type things into the cells in a row.
    From how I understand it, a "record" is a "row" right?

    Duplicate records: Good point. I hadn't considered that. However, it could be a duplicate record. It just needs to append to the file.
    Changed record: Hmmm, another good point. No wonder people just buy software for this sort of thing instead of trying to make it happen in Excel.


    Long story short: My boss doesn't want to pay for performance management software ("http://www.successfactors.com/en_us.html"). Instead he wants to take a few of the features that he wants and try to emulate that using a combination of shared folders on the server and Excel. So if he wants to enter a new goal for an employee, he would do so on his master Excel file. That goal would then show up on the employees file in a protected folder. Then the employee could comment and that would somehow show up on his master file. The tricky part to this is making it so that depending on which employee he enters will depend on which file the record is appended to.

    Personally I don't think this is going to happen. But if there is a simple way to do this, then I would like to try it.

  6. #6
    Senior Member CeeBee's Avatar
    Join Date
    Jan 2014
    Posts
    1,677
    vCash
    1792
    Points
    141,465
    Bank
    0
    Total Points
    141,465
    Donate
    Excel is then the wrong tool. The permissions issue will be a killer, all employees would need to be able to modify his master file which means they would all have access to each other's data. BAAAAAAAD....
    Not to mention no audit trail...
    This would work much, much easier with a web interface and a database. However, going any of these routes will be time consuming and time=money. Maybe more than the cost of the software. I can see the web/db route taking about 80h of development for something minimal but usable - multiply by the hourly rate of an in-house programmer and see how the costs compare.
    Also chances are that there is already an open source product which does just that...

  7. #7
    Senior Member Webhead's Avatar
    Join Date
    Jan 2014
    Posts
    1,829
    vCash
    500
    Points
    680,071
    Bank
    0
    Total Points
    680,071
    Donate
    Quote Originally Posted by CeeBee View Post
    Excel is then the wrong tool. The permissions issue will be a killer, all employees would need to be able to modify his master file which means they would all have access to each other's data. BAAAAAAAD....
    Not to mention no audit trail...
    This would work much, much easier with a web interface and a database. However, going any of these routes will be time consuming and time=money. Maybe more than the cost of the software. I can see the web/db route taking about 80h of development for something minimal but usable - multiply by the hourly rate of an in-house programmer and see how the costs compare.
    Also chances are that there is already an open source product which does just that...
    I totally agree. The problem is that users always have the mindset of, "I just want to be able to [do_some_task]." In their minds it's a simple task. They never realize how much goes into making something seem so simple. Or they always want me to provide some high-end, enterprise level piece of amazingness with zero budget. Like dude,... I'm a sys admin, not a magician. I think what happens is that they meet with people from other businesses, learn about some cool thing they are doing and then see if I can make it happen. They never realize that it's usually very expensive to do these things.

    Anyway, to hear this coming from you confirms my original beliefs then. I also got confirmation from the guy who does development here. He wasn't tasked with this because he's busy with other stuff. But when I asked him about it, he had a look on his face that said, "Yes, it can be done but it's going to be a lot of work".

    I'm going to just look into finding something open source and see if I can find a close match. I found a thing called "WaypointHR". Stuck it on my LAMP server and tried it out. It's not bad. It's very British though (they spell "color" as "colour" for example). So that might be a little different. I'm sure there has to be other better stuff out there.

    Another option is that I have a Sharepoint server setup. But it's old. It's the old Sharepoint Services 3.0 running on a Windows 2003 server. So I really would rather not use that antiquated software. Although it would probably do what he wants to do so I'll use that as a last resort option I suppose.

  8. #8
    Senior Member Webhead's Avatar
    Join Date
    Jan 2014
    Posts
    1,829
    vCash
    500
    Points
    680,071
    Bank
    0
    Total Points
    680,071
    Donate
    Well, I guess nevermind. I discovered OrangeHRM. Seems promising. I have it installed and going to run it by my manager.

  9. #9
    Junior Member
    Join Date
    Jan 2014
    Posts
    2,149
    vCash
    0
    Points
    424,012
    Bank
    0
    Total Points
    424,012
    Donate
    Thank god I work with an Excel nerd.

  10. #10
    Senior Member Webhead's Avatar
    Join Date
    Jan 2014
    Posts
    1,829
    vCash
    500
    Points
    680,071
    Bank
    0
    Total Points
    680,071
    Donate
    Quote Originally Posted by Larommi View Post
    Thank god I work with an Excel nerd.
    I wonder what your Excel nerd thinks about this project.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •