Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Location
    Ontario, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Database update (Excel2000/vba)

    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 <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    New Lounger
    Join Date
    Aug 2002
    Location
    Ontario, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Database update (Excel2000/vba)

    Just what I needed. Thanks a lot. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Database update (Excel2000/vba)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Database update (Excel2000/vba)

    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.

  5. #5
    New Lounger
    Join Date
    Aug 2002
    Location
    Ontario, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Database update (Excel2000/vba)

    Caught it . Thanks

    Mary

Posting Permissions

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