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.

Call Excel Workbook in VBA

Discussion in 'Business Applications' started by smody121, Sep 16, 2011.

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

    smody121 Thread Starter

    Joined:
    Aug 19, 2010
    Messages:
    65
    I have two workbooks I'm trying to combine in a VBA macro. The first excel spreadsheet is just simply a sheet of 30 columns of data and 5k rows. It adds a new row every day.

    The second sheet will be the model with the macro. I would like to write a script that will use a sort of vlookup to the data sheet to pull in specific parts of the datatable instead of searching through the whole big sheet.

    For example, see attached. The Model tab lets you input a date or dates in the C column and then it would have to somehow search another WORKBOOK, not within this one as the example shows (for simplicity). Any ideas would be much appreciated.
     

    Attached Files:

  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    The code below should work for you assuming the layout in your sample workbook is the same as your real workbooks. Just change the highlighted lines of code to reflect the correct names of your workbooks and worksheets. For simplicity sake I named the workbooks and the sheet names the same. The lookup range is dynamic and automatically calculates the last row so no changes have to be made to the code as new rows are added. There is no error handling added yet so if a match is not made the macro will crash. We can fix that later if you choose but I want to make sure the macro does what you want first. See attached sample workbooks. Obviously both workbooks need to be open in order for the macro to work.

    Code:
    Sub GetMMD()
    
    Dim aArray As Variant
    
    vWB = "[COLOR="Red"]Model.xls[/COLOR]"
    vSheet = "[COLOR="Red"]Model[/COLOR]"
    
    vWB2 = "[COLOR="Red"]Data.xls[/COLOR]"
    vSheet2 = "[COLOR="Red"]Data[/COLOR]"
    
    For i = 6 To Workbooks(vWB).Sheets(vSheet).Cells(Rows.Count, "C").End(xlUp).Row
    
    vRow = Application.Match(CLng(Workbooks(vWB).Sheets(vSheet).Range("C" & i).Value), _
    Workbooks(vWB2).Sheets(vSheet2).Range("A1:A" & Workbooks(vWB2).Sheets(vSheet2).Cells(Rows.Count, "A").End(xlUp).Row), 0)
    
    aArray = Workbooks(vWB2).Sheets(vSheet2).Range("B" & vRow & ":" & "AE" & vRow).Value
    
    x = 34
    
    For Each vItem In aArray
    
    Workbooks(vWB).Sheets(vSheet).Cells(i, x).Value = vItem
    
    x = x - 1
    
    Next vItem
    
    Next i
    
    End Sub
    
    Rollin
     

    Attached Files:

  3. smody121

    smody121 Thread Starter

    Joined:
    Aug 19, 2010
    Messages:
    65
    As always, Rollin you the man. works perfectly for me, I seemed to be overthinking the calling of the workbook part since I thought I had to start with the whole name (drive\Folder1\Folder2\Folder3\Folder4\Folder5\FileName.xls or something like that) but your way is much cleaner and simpler.

    I would like to error check some if possible. I created another Form to click on just to validate the dates entered as being something that's possible to pull. The macro would vlookup or search each date inputted from the Model and check it against the Data workbook. I was thinking if it was not a match we could shade the background of the cell red to make it stand out otherwise if all dates are valid we can put the words "ALL DATES VALID" in D5 or D5:D7 with one word in each cell.

    I attached a kind of template of what it may look like.

    Thanks!
     

    Attached Files:

  4. smody121

    smody121 Thread Starter

    Joined:
    Aug 19, 2010
    Messages:
    65
    Range("XY").Select
    With Selection.Interior
    .ColorIndex = 3
    .Pattern = xlSolid
    End With

    This turns the cell red. That's my pathetic contribution.
     
  5. smody121

    smody121 Thread Starter

    Joined:
    Aug 19, 2010
    Messages:
    65
    Here is my attempt:

    Code:
    Sub Validates()
     
    'No screen spazzing
    Application.ScreenUpdating = False
     
    Workbooks.Open Filename:="[URL="file://\\nasny31v4cap\Daily"]\\nasny31v4cap\Daily[/URL] Charts\Market Data.xls", ReadOnly:=True
    Workbooks("Rates Model.xls").Activate
     
        Dim MDDates As Date
    MDBook = "Market Data.xls"
    MDSheet = "MMD"
    RatesBook = "Rates Model.xls"
    RatesSheet = "Rates Lookup"
     
    'Find bottom of Market Data dates
    Set curr = MDBook.MDSheet.Range("A2")
    endMDRow = MDBook.MDSheet.Cells(Rows.Count, curr.Column).End(xlUp).Row
    Set whereLook = MDSheet.Range("A2:A" & endMDRow)
     
    'Dynamically count the dates starting in the 5th row
    For i = 5 To Workbooks(RatesBook).Sheets(RatesSheet).Cells(Rows.Count, "C").End(xlUp).Row
        If Application.VLookup(Workbooks(RatesBook).Sheets(RatesSheet).Range("C" & i).Value, _
            Workbooks(MDBook).Sheets(MDSheet).Range("A1:A" & Workbooks(MDBook).Sheets(MDSheet).Cells(Rows.Count, _
            "A").End(xlUp).Row)) = True Then
                'Do nothing
            Else
                Workbooks(RatesBook).Sheets(RatesSheet).Cells("D" & i) = 1
            End If
     
    Next i
     
    Workbooks("Market Data.xls").Close
    End Sub
    
    This is not right obviously. I think I'm overthinking it...
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Did you get this sorted yet?

    Rollin
     
  7. smody121

    smody121 Thread Starter

    Joined:
    Aug 19, 2010
    Messages:
    65
    Not yet, I'm having trouble adding an error check. I tried the On Error Resume Next and a few others but they didn't fix the problem, just ignored it. Just want to skip over the inputs that don't work, and put a 1 in the cell column next to it.
     
  8. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Can you email or post your updated sample workbooks with the existing code attached and I'll take a look. If you decide to email instead of posting please use the following email addy: rollin_again at hotmail dot com

    Rollin
     
  9. 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/1017994

  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