Excel Database update (Excel2000/vba)

Topic

New Reply

I am opening an Excel database and extracting data to use in my program. How do I get it to update on open? This doesn’t seem to work.

Sub GetLastOrderRow(LastRowO As Long)
Workbooks.Open Filename:=”C:ReportsDump Order.xls”, updatelinks:=3, ReadOnly:=False
Sheets(“Order”).Activate
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
On Error Resume Next
LastRowO = Cells.Find(“*”, Sheets(“Order”).Range(“A1”), xlFormulas, , xlByRows, xlPrevious).Row
End Sub

Mary

Reply | Quote

Replies

What do you mean by an Excel database? If you mean a table that is linked to a table or query in an external database (for example an Access .mdb), you can use the QueryTables collection:

Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks.Open(Filename:=”C:ReportsDump Order.xls”)
Set ws = wb.Worksheets(“Orders”)
ws.QueryTables(1).Refresh

If you mean something else, please explain.

Reply | Quote

Just what I needed. Thanks a lot.

Reply | Quote

I hope that you noticed that I mistakenly swapped ws and wb in my example as posted originally; I have corrected it now. Sorry about that.

Reply | Quote

Caught it . Thanks

Mary

Reply | Quote