1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Need help Excel 2 modify other workbook

Discussion in 'Business Applications' started by vitalspec, Apr 6, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. vitalspec

    vitalspec Thread Starter

    Joined:
    Dec 24, 2011
    Messages:
    11
    Hello guys.
    Please guide me through building my own tool in Excel.
    I need to have workbook WK 1 with macro to modify another workbook WK2 which already have macros in it.
    That is my plan : WK1 should have several command buttons first is to find person by ID from WK 2 , copy entire row with persons details to WK1 list 1, another button same but copy entire row to WK1 list 2, than when both lists created, I must have third command button which will : for each person in List1 WK1 will change value in cell 1 in WK2 from none to 1 and cell 10 enters =today date, for each person in List2 WK1 will delete value in first cell of the row in WK2.

    I am keep loosing the track of what would be most simple way to do those steps, WK 2 file name is changing every day with date in beginning of the name of file,
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    Hi, for starters I suggest you tell us which version of Excel you're using.
    Have you tried recording a macro following the steps you describe, and onec the recording is stopped you can then see how it is (more or less) done and the final step would be fine tuning the macro so that it works for all cases.
    I'm sure it can be done.
    Let me see if I understand your question:
    You have two files WK1 and WK2
    WK2 is the file that contains all the source data? The macros in WK2 do they have anything to do with this or this for WK2 only?
    You open WK1 and you need three (3) buttons:
    Button1 is the button that will srat a macro that asks you for an ID to search for in WK2?
    If this ID is found (how many times can the same ID be present?)
    I asume it is unique so if only one and the ID is found you want to copy the row of data to WK1, you say List 1, is that a sheet or a list in a sheet?
    Button 2 is to copy this new row from List 1 to List 2, (another sheet or in the sale sheet?)
    And, why two buttons,m it could be done all in the same macro.
    ID found -> Copy row from WK2 to WK1 List 1 and then copy from WK1 List 1 to WK1 List 2
    Button 3 for each person (?) in WK1 List 1 change a value in Column A WK 2 from 0 to 1 and Column J (10) today's date and in WK1 List 2 clear column A ?

    Why 3 buttons all can be done in one run.

    Have you tried this already yourself?
     
  3. vitalspec

    vitalspec Thread Starter

    Joined:
    Dec 24, 2011
    Messages:
    11
    Thanks for reply. More info: Computers running Excel 2007
    The reason for three buttons is that situation can change before I will press the last button, which will make update in WK2.
    The scripts in WK2 are working and have nothing to do with WK1, I am afraid to add more scripts to WK1 . Therefore think another Workbook with scripts will be better
    I did not posted any code cause I mentioned earlier I am confused, and want to choose best plan first.
    So what would be Your advise at this : How do I call for another Workbook, WK1 file name are changing every day as dates changes, with date in filename. I come up with some thing like this :
    Dim strFile As String
    Application.ScreenUpdating = False
    strFile = Application.GetOpenFilename
    If CStr(strFile) <> "False" Then
    Set shtOrigin = Workbooks.Open(strFile).Sheets(2)
    Else
    Application.ScreenUpdating = True
    MsgBox "No valid file selected", vbOKOnly + vbInformation, "Copy Error"
    End If
    Or maybe ther is easier way? Maybe this :
    Private Sub UserForm_Activate()

    'Populate list box with names of open workbooks.

    Dim wb As Workbook

    For Each wb In Workbooks

    ListBox1.AddItem wb.Name

    Next wb

    End Sub

    As I said I need advice on strategy , what would be most easiest way to do my task?
    Thank You in advance
     
  4. vitalspec

    vitalspec Thread Starter

    Joined:
    Dec 24, 2011
    Messages:
    11
    Another question : When search for criteria in worksheet in other workbook and than copying row to first Workbook list 1 or 2 , each time switching between workbooks or it is better to copy whole sheet from WK2 to WK1 then do searching and changes with and than paste it back to WK2 , which scenario would simpler?
    Thanks.
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    The scenario which would be simpler? I don't know. Depends on all you want to do

    I've attached two workbooks, WBOOK1.xlsm and WBOOK2.xlsm
    Place these in the same folder and open WBOOK1, press the button and see that WBOOK2 opens.

    It doesn't do anything else.

    The macro is simple it assignes the variales and opens the other Workbook.

    Once that is understood then you can addd your own macro's and address WBOOK2 or whatever it will be called and do the precseeing, also do not forget to return to WBOOK1 for the copying and so.

    Just see it you can start with this. :)
     

    Attached Files:

  6. vitalspec

    vitalspec Thread Starter

    Joined:
    Dec 24, 2011
    Messages:
    11
    So I started with one of the option I found , it works fine , since file name and location can be different using GetOpenFilename seems logic :
    Please get me out of my dead end , how do I search now for criteria? Lets say input box , where user will enter 6 number unique ID and search Workbook2 column "K" , for same value in Sheet2 named "all" , then copy entire row, for copying in to Workbook1
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    There are many ways to do a find.
    The simplest one is the use of an Input Box
    Excel has (in vba) two options: InputBox and Application.InputBox, I personally use this last one due tue the fact that you can force the entry to be only numeric
    Take a look at the links below for more information
    http://www.cpearson.com/excel/FindAll.aspx
    http://www.cpearson.com/Excel/Topic.aspx
    http://msdn.microsoft.com/en-us/library/office/ff839468.aspx
    http://www.ozgrid.com/forum/showthread.php?t=20105

    I think there's a lot of information there.
    Perason's site is a good source of scripts and samples which I oftently use.

    Let me know if you need more help, I suggest then that you attach a sample file with what you have so far
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    I corrected your code so that it does not generate any errors
    I cannot test anything because I don't have any data to use

    Code:
    Option Explicit               ' I always set Option Explict so that ALL variables have to be dimensioned correctly
    
    Sub SearchAndAdd()
    
    Dim LSearchRow      As Integer
    Dim LCopyToRow      As Integer
    Dim LSearchValue    As Variant
    Dim getNumber       As Long
    
    On Error GoTo Err_Execute
       
    LSearchValue = InputBox("Enter Ticket Nr.", 1)
    
    If IsNumeric(LSearchValue) Then
        If Not (Len(Trim(LSearchValue)) = 6) Then MsgBox "Not 6 numbers Ticket?":  Exit Sub
    Else
        MsgBox "Invalid Number"
        Exit Sub
    End If
    
    getNumber = CLng(LSearchValue)
       
    'Start search in row 2
    LSearchRow = 2
       
    'Start copying data to Destination row  (row counter variable)
    LCopyToRow = 4
       
    Workbooks(2).Activate
    Sheets(2).Select
       
    While Len(Range("K" & CStr(LSearchRow)).Value) > 0
       
        'If value in column K = "515246", copy entire row to Sheet2
        If Range("K" & CStr(LSearchRow)).Value = LSearchValue Then
          
             'Select row in Sheet1 to copyl
             Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
             Selection.Copy
             
             'Switch back to another workbook
             Workbooks(1).Activate
             Sheets(1).Select
             
             'Paste row into Sheet2 in next row
             Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
             ActiveSheet.Paste
             'Move counter to next row
             LCopyToRow = LCopyToRow + 1
             MsgBox "Found & Added"
        End If
        LSearchRow = LSearchRow + 1
          
    Wend
        
       'Switch back to another workbook
    Workbooks(1).Activate
    Sheets(1).Select
       
    'Position on cell A3
    Application.CutCopyMode = False
    Range("A3").Select
       
       
    Exit Sub
       
    Err_Execute:
    MsgBox "An error occurred."
       
    End Sub
    
     
  9. vitalspec

    vitalspec Thread Starter

    Joined:
    Dec 24, 2011
    Messages:
    11
    First of all thank for corrections.

    Still struggling with issues, off course. For me is takes more time for searching, reading and thinking , than trying , and then..... error, debug... But that is my way of learning.
    I am attaching 2 test workbooks
    At the moment OpenFile module works, but when no file chosen returning error False.xls not found , I guess my mistake somewhere here :
    In module SearchAndCopyRow To go on with project I chose temporary solution to switch between workbooks which is
    , it works , but as I understood if another workbook will be open before that, they would change their positions.
    So now code You posted does not returning errors , but it does not finding and copying. I am sure with previous one it did , but there was errors. And I am still struggling to get message boxes at the exit, lets say if works - "success" if not "not found" , as You have seen previous code was returning msgBox success in all cases.
    Then I am going to search, read, compile and struggle with module where command button Nm.4 will go through list 1 in Workbook1 and find same rows in Workbook2 to change value in A column from none to 1, and list 2 will do opposite change column A value from 1 to none. Any suggestions will be apreciated.
    Thanks to everyone here is a lot of info and examples to teach for me ...
     

    Attached Files:

  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    I'll take a look during the weeken and see if I can help you along :)
     
  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    I changed you macro to the way I do these things, there are many ways and this one has proven right for me so far.
    The green button now starts the SearchAndAdd macro but at the same time it also asks for the file, I mean you do not open a file and look for another macro and do something else

    I do not knwo if the tickets are duplicated, I think not if I see the data you left behind somewhere around in row 3240.

    The macro will now ask you to select a file, if nothing is selected it will ask if you want to select for another file
    One you select a file you can enter the ticket number, if invalid or incorrect you will be asked if you want to enter another tickte, if not it will close the file and ask if you want to select another oner.

    I suggest you try it out and if you have questions let me know.
     

    Attached Files:

  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    Hi,
    If the Ticket Nr is always unique, then you can remove some extra lines in the vba code:

    Code:
    With wsData.Range("K:K")
        Set rng = .Find(What:=getNumber, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
        If Not rng Is Nothing Then
            LSearchRow = rng.Row
    [COLOR="Red"]        Do
                If wsData.Cells(rng.Row, "K").Value = getNumber Then[/COLOR]
                    wsData.Cells(rng.Row, "A").EntireRow.Copy
                    Cells(LCopyToRow, "A").PasteSpecial
                    LCopyToRow = LCopyToRow + 1
                    Application.CutCopyMode = False
                    Range("A" & LCopyToRow).Select
                    MsgBox "Found & Added"
    [COLOR="red"]            End If
                Set rng = .FindNext(rng)
            Loop While Not rng Is Nothing And rng.Row <> LSearchRow[/COLOR]
        End If
    End With
    
    Remove the red lines, the result will be as shown below
    Code:
    With wsData.Range("K:K")
        Set rng = .Find(What:=getNumber, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
        If Not rng Is Nothing Then
            LSearchRow = rng.Row
            wsData.Cells(rng.Row, "A").EntireRow.Copy
            Cells(LCopyToRow, "A").PasteSpecial
            LCopyToRow = LCopyToRow + 1
            Application.CutCopyMode = False
            Range("A" & LCopyToRow).Select
            MsgBox "Found & Added"
        End If
    End With
    
    
    Fot the rest, you explanation in the PM tells me nothing of what you are really doing.
    I would never keep a file open if it can be accessed by others, but without really understanding what your goal is I have no idea what hints or ideas I can give you.

    What changes are you making where?
    List 1 or 2, ???

    Well many questionmarks ....
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1095346

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice