Problem with VB that pulls data from files containing ODBC links

[bkaehny]

I've got four files from which I'm pulling data via some simple VB. Each of these files contains a table that's linked via ODBC to a View. These tables are all set to "refresh on open." When I open these files normally, the data does indeed refresh. However, when I open these files using VB, the data doesn't seem to refresh. Any ideas on what to do?

I thought the problem might be that the VB is going so fast, it's not giving the files enough time to refresh. So I tried inserting the following code after each Workbooks.Open command:

Application.Wait Now + TimeValue("00:00:20")

That doesn't seem to work, however. Any help you can give me would be greatly appreciated. Thank you.

 

[sous2817]

not sure if this makes a difference, but do you have events turned off when you run your code?

 

[bkaehny]

Not that I'm aware of. This is how my macro begins:

Application.ScreenUpdating = False

Workbooks.Open Filename:= _

 

[sous2817]

It may be worth posting your entire code if you can.

 

[bkaehny]

It's the beginning part that's giving me trouble. "file A", "file B", "file C", and "file D" are the four files that contain linked table data. "master file" is the file that houses the macro, and it's the file that I'm copying and pasting data into.



Sub Macro()

Application.ScreenUpdating = False


Workbooks.Open Filename:= _
"file A.xlsx"
ActiveWorkbook.RefreshAll
Application.Wait Now + TimeValue("00:00:20")
ActiveWorkbook.Save
Workbooks.Open Filename:= _
"file B.xlsx"
ActiveWorkbook.RefreshAll
Application.Wait Now + TimeValue("00:00:20")
ActiveWorkbook.Save
Workbooks.Open Filename:= _
"file C.xlsx"
ActiveWorkbook.RefreshAll
Application.Wait Now + TimeValue("00:00:20")
ActiveWorkbook.Save
Workbooks.Open Filename:= _
"file D.xlsx"
ActiveWorkbook.RefreshAll
Application.Wait Now + TimeValue("00:00:20")
ActiveWorkbook.Save

Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("master file.xlsm").Activate
Sheets("Invoice Lines").Select
Range("A2").Select
ActiveSheet.Paste
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LR + 1).Select




Windows("file A.xlsx").Activate
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("master file.xlsm").Activate
ActiveSheet.Paste
LR2 = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LR2 + 1).Select



Windows("file B.xlsx").Activate
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("master file.xlsm").Activate
ActiveSheet.Paste
LR3 = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LR3 + 1).Select


Windows("file C.xlsx").Activate
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("master file.xlsm").Activate
ActiveSheet.Paste

Windows("file A.xlsx").Activate
ActiveWindow.Close
Windows("file B.xlsx").Activate
ActiveWindow.Close
Windows("file C.xlsx").Activate
ActiveWindow.Close
Windows("file D.xlsx").Activate
ActiveWindow.Close

Application.ScreenUpdating = True

End Sub

 

[sous2817]

Rather than using the "refresh on open" functionality that you mentioned in your first post, would you be able to explicitly refresh the data in your macro?

 

[bkaehny]

That seems to have worked. I also replaced the "ActiveWorkbook.RefreshAll" code with "Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False" to do the refresh inside the VB.

Thanks for your help!

 

[sous2817]

Outstanding, glad I could help! Best of luck with the rest of your project!