VBA Excelobj.UpdateLinks not working with late binding

Question

Ok, I am trying to do the following with Excel using Access VBA:

1. open
2. update links
3. save
4. close
5. quit

With Early binding I have no problems using xlBook.updatelinks = xlupdatelinksalways.  However, when I use late binding that doesn't work anymore.  Am I missing something really easy here?

'...................................
    Set xlbook = GetObject(strSavLoc & SchedName)
    Set xlApp = xlbook.Parent
    xlbook.refreshall  '<~not doing anything either
    'xlbook.UpdateLinks = xlUpdateLinksAlways '<~doesn't work with late binding
    xlbook.Save
    xlbook.saved = True
    xlbook.Close
    Set xlbook = Nothing
    xlApp.Quit
    Set xlApp = Nothing
'...................................

thanks for the help in advance.

Answer 1

This depends on what you are trying to do.  It sounds like you are using Excel as a data entry source.  If this is the case -- then this is redundat and I would create a form in Access to perform the same operation.  If you are receiving Excel files on a continuous basis -- I would import the Excel data into Access instead of linking to it.

I realize that there is the linked workbook feature of Excel to Access.   But I think what MS was thinking was that Access would be the datasource for charts (or whatever) to Excel.   Using workbook linking in the other direction is kind of redundant -- in my opinion.   The point being -- you are probably not missing anything - just creating redundancy -- which eventually leads to problems as you are encountering.  The fix -- change your M.O. here.

---

Rich P

Answer 2

I guess I left some details out, this is done in Access VBA

1. open excel workbook
2. update excel workbook links
3. save excel workbook
4. close excel workbook
5. quit excel
6. transferspreadsheet of updated excel workbook to access table

A little more detail is that there are schedules in excel (can't be changed) that are not table friendly.  I linked all the data needed into one excel file so I can easily transfer the data to over to Access with 'TransferSpreadSheet'.

I am trying to move Access code over from early binding to late binding.  Issue is that the code that worked in early binding is not working with late binding (namely: xlbook.UpdateLinks = xlUpdateLinksAlways)  The links in the workbook need to be updated before the transfer to Access.  That is pretty much the reason for the process I currently using.  If during the spreadsheet transfer, the links updated, I would be golden.

Answer 3

Instead of linked workbooks, worksheets -- you should consider using ADO (ADODB).  You can either read data from Excel to Access (from within Access) or you could write data to Access from Excel.  ADO is quite a bit more versatile and reliable than Transferspreadsheet.  Since you seem to have activity within Excel you could write to Access -- something like this (this requires a reference in Tools/References to Microsoft ActiveX Object Library 2.x  -- needs to be 2.5 or higher):

Sub WriteToAccess()
   '--this is a procedure from a code module in Excel

   Dim cmd As New ADODB.Command, RS As ADODB.Recordset, rng As Range, i As Integer
   Set rng = Sheet1.Range("A1:C10")
   cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=;Data Source=C:\code\testacc\db1test.mdb"

   Set RS = New ADODB.Recordset
   RS.Open "tblExcelTest", cmd.ActiveConnection, adOpenDynamic, adLockOptimistic, -1
   For i = 1 To rng.Rows.Count
      RS.AddNew
      RS(0) = rng(i, 1)
      RS(1) = rng(i, 2)
      RS(2) = rng(i, 3)
      RS.Update
   Next
   RS.Close

End Sub

in my sample here tblExcelTest is a table in Access that contains 3 (text) fields (3 columns).  rng is an Excel Range object to read the contents of range("A1:C10") -- 3 columns and 10 rows.

---

Rich P

Answer 4

Rich,

Thanks for your resonse.  Will this update the formulas in excel when it's cycling through the cells? The updating is where the issue is and I don't see where in the module it updates the formulas in Excel.

thanks for the help

Answer 5

No, this code does not update formulas.  As for formulas -- do you mean does the code change/modify the formulas or does it trigger the formulas to update the data in the respective cells? 

Quick lesson on Excel formulas -- once you have a formula in place  in a cell -- it should remain static -- that means that the formula is pretty much set in stone (so to speak) and only the data changes (per for formula(s)).  If you have to keep changing/modify the formula structure -- you need to rethink the design.  Formulas are supposed to be a static thing -- of course, you can update a formula as necessary (once in a while) but not change/modify the formula(s) continuously. 

The code sample will read whatever is in the data cell.  The formulas update the data in the cells automatically -- instantaneously (if written correctly).  If the data in a cell is not updating correctly -- you many not have accounted for some condition in the data.  Then yes, you need to update your formula.  This is not an uncommon problem, usually caused by null values that have not been accounted for or zero/blank values -- or occassionally unusual symbols or numbers that came in as text, dates that are text, the list goes on.

---

Rich P

Answer 6

Have you declared a value for xlUpdateLinksAlways? (it should be 3) With late binding, you have to declare all of the intrinsic Excel constants that are defined in the Excel object to which you link with early binding.

If, in fact, that's the solution to your problem, it indicates to me that you haven't told Access to require that all variables be declared. To see whether that's true, check whether there's a line Option Explicit at the top of your module. To have Access automatically add that line in all future modules, go into the VB Editor and select Tools | Options from the menu and ensure the checkbox Require Variable Declaration on the Editor tab is checked. This will only work on future modules, though: you'll have to add the line to all existing modules.

While it may seem like a pain to have to declare all variables, doing so can save you literally dozens of hours of debugging work when you've mistyped a name or, as in this case, tried to use a constant that hasn't been defined.

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 7

Rich, I am trying to update the formulas which are links to other excel workbooks.  this is so when the workbook is transfered, i get all the updated info.

Doug, not sure what to declare updatelinks.  Do I need to declare say:

Dim uplinks as (object or int?)

Then something like

Xlbook.uplinks?

Answer 8

Const xlUpdateLinksAlways As Long = 3
Dim xlbook As Object
Dim xlApp As Object
  
  Set xlbook = GetObject(strSavLoc & SchedName)
  Set xlApp = xlbook.Parent
  xlbook.UpdateLinks = xlUpdateLinksAlways
  xlbook.Save
  xlbook.saved = True
  xlbook.Close
  Set xlbook = Nothing
  xlApp.Quit
  Set xlApp = Nothing

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 9

Thanks Doug.  I'll try that when I get back in the office on Monday.

Thanks again.

Answer 10

@Doug, That didn't work either.  I'm thinking it may have something to do with the trusted documents and macro security, but I'm not sure (just a shot in the dark). 

Here is the code:

Dim FileToOpen As String
Dim strSavLoc As String
Dim SchedName As String
Const xlUpdateLinksAlways As Long = 3
Dim xlApp As Object
Dim xlbook As Object
    FileToOpen = "**************\Reports\Shift Report Database"' <- Change this string
    strSavLoc = Environ("TEMP")
    SchedName = "\seatSchedule1.xls"
    
    'make dir if dir doesn't exist
    If Not FolderExists(strSavLoc) Then
        MkDir strSavLoc
    End If
    
    If FileExists(FileToOpen & SchedName) Then 'does file already exist
        If Not FileExists(strSavLoc & SchedName) Then 'does file exist in save location
            'no, copy file
            FileCopy FileToOpen & SchedName, strSavLoc & SchedName
        Else 'file exists,kill previous and copy
            SetAttr strSavLoc & SchedName, vbNormal
            Kill (strSavLoc & SchedName)
            FileCopy FileToOpen & SchedName, strSavLoc & SchedName
        End If
    Else 'can't find file
        MsgBox "Could not find Schedules", vbCritical, "File not found"
        GoTo Exit_UpDateData
    End If
        
    Set xlbook = GetObject(strSavLoc & SchedName)
    Set xlApp = xlbook.Parent
    xlbook.UpdateLinks = xlUpdateLinksAlways
    xlbook.Save
    xlbook.saved = True
    xlbook.Close
    Set xlbook = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    
 '   intCounter = intCounter + 25
 '   acbUpdateMeter(intCounter)
    
    DoCmd.SetWarnings (False)
    If TableExists("lnkSchedules") Then
        CurrentDb.Execute ("Delete * from lnkSchedules;")
    End If
    
'    intCounter = intCounter + 25
'    blnOK = acbUpdateMeter(intCounter)
        'import schedule
        DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel7, "lnkSchedules", _
strSavLoc & SchedName, True, "A1:I1500"
                    
 '   intCounter = intCounter + 25
 '   blnOK = acbUpdateMeter(intCounter)
Exit_UpDateData:
    Call acbCloseMeter
    If FileExists(strSavLoc & SchedName) Then
        SetAttr strSavLoc & SchedName, vbNormal
        Kill (strSavLoc & SchedName)
    End If
    'clean up any import error tables
    Dim intLoop As Integer
    For intLoop = (CurrentData.AllTables.Count - 1) To 0 Step -1
        If CurrentData.AllTables(intLoop).Name Like "*_ImportError*" Then
            DoCmd.DeleteObject acTable, CurrentData.AllTables(intLoop).Name
        End If
    Next intLoop
    DoCmd.SetWarnings (True)
    DoCmd.Hourglass (False)
    Exit Function
Err_UpDateData:
Call acbCloseMeter
DoCmd.SetWarnings (True)
DoCmd.Hourglass (False)
Select Case Err
      Case 3349
         MsgBox "The is an Error in the Shedule Link." & _
         vbCr & "Please call Production Admin (x20214) or Database Admin (x20156)." _
         , , "Invalid Input in Reports  Error -" & Err.Number
      Case Else
         MsgBox "UpdateData() Error: " & Err.Number & " - " & Err.Description
   End Select
End Function

I don't understand why this isn't updating.

Thanks again for the help  (I know the code needs cleaned up)

Answer 11

What error do you get?

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 12

No errors.  The excel sheet didn't update the formulas, which I don't understand because the xlUpdateLinks is in the code.

Any suggestions?

Answer 13

>>

No errors.  The excel sheet didn't update the formulas, which I don't understand because the xlUpdateLinks is in the code.

<<

When you say Update Formulas -- do you mean updating the data presented by the formulas or do you mean Modifying the formulas themselves?

---

Rich P

Answer 14

Rich,

I am updating the links which in turn should update the formula's calculations.

for example (in excel):

 =IF('Accord Schedule'!B24=0,"Check",'Accord Schedule'!B24)

Answer 15

AFAIK, UpdateLinks doesn't affect If statements such as what you show. I believe you need to issue a refresh (F9 does it manually), but I'm afraid I don't know what the corresponding code would be.

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 16

Doug,

Yes that is exactly what I am trying to do on top of UpdateLinks. 

I even added:  xlbook.RefreshAll

It didn't work either.  I just don't get it because I had no problems when I was using early binding.

Answer 17

Sorry, no idea. I've never experienced any differences using Late Binding.

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 18

Thanks for the help again (You seem to always bail me out.)  I am going back to the early binding until I can figure it out.  Of course, if I figure it out, I'll stick it on here.  Thanks again to you and Rich for helping me.

Answer 19

Note:  Formulas are Excel's equivalent to queries in Access.   You would find it quite a bit easier and more reliable to read the raw Excel data into Access tables and then write the equivalent Access queries (equivalent to the Excel formulas) to read the processed data.  Just forgo, bypass the linked workbooks.  As you are already experiencing -- linked workbooks are hard to work with.  You could either write the data to Access from Excel with the code sample I gave you earlier, or you could read the data directly from Excel using ADO (I need to work up an example how to do that with ADO from Access reading from Excel).

---

Rich P

Answer 20

OK.  Here is some ADODB code for reading data contained in an Excel Spreadsheet to an Access dataTable.  This sample uses the entire UsedRange of Sheet1 -- Sheet1$  (the $ means use all of the used range of the sheet).  Again, in Access make a reference to Microsoft ActiveX Object Library 2.x (2.5 or higher).  In this sample my test .xls file contains 3 columns of data and 10 row (the rowcount is irrelevant here).  I created a table in Access which contains 3 fields (columns) -- to match up with the Excel Sheet.

Sub ReadFromExcelADO()
   Dim oConn As New ADODB.Connection, oRS As New ADODB.Recordset
   Dim RSdao As DAO.Recordset, i As Integer
   oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\Code\testAcc\WriteToAccessFromExcel.xls;" & _
           "Extended Properties=""Excel 8.0;HDR=NO;"""
          
   '--this one does not have HDR=NO -- means 1st row in range gets treated as header row
   '--this connection string will return one less row of data than the first one
   'oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   '        "Data Source=C:\Code\testAcc\WriteToAccessFromExcel.xls;" & _
   '        "Extended Properties=""Excel 8.0;"""
          
   oRS.Open "Select * from [Sheet1$]", oConn, adOpenStatic
   Debug.Print oRS.RecordCount
   Set RSdao = CurrentDb.OpenRecordset("tbl1")
   For i = 0 To oRS.RecordCount - 1
      RSdao.AddNew
      RSdao(0) = oRS(0)
      RSdao(1) = oRS(1)
      RSdao(2) = oRS(2)
      RSdao.Update
   Next
   oRS.Close
   oConn.Close

End Sub

---

Rich P

Answer 21

Thanks for the suggestion Rich, but at this time I can't go through and convert all the excel links (ranges) over for Access to cycle through the rows.  (not to mention the columns change without notification)  I have done what you are suggesting before in other frontends I've built and it works well.  This issue is just going to have to wait  to be fixed.

Thanks again for you help.

Answer 22

I think I may have figured out what the issue is, the TrustCenter (completely forgot). 

In the routine I am copying the file to the Environ("TEMP") directory, due to someone having the file open, so the file can be opened and saved successfully (thus updating the links).  I am thinking that since the copy is a new file TrustCenter thinks the copy is not a "Trusted" file thus not updating links (I believe that microsoft marked Links as a security risk in office 2007.)  Another issue is the Temp folder is marked as an untrusted location by default in Trust center.

Previous routine (minus file checks and whatnot to keep it simple):

Copy original file to temp dir

Open copy

Save copy

Close Obj

Quit Obj

Transferspreadsheet Copied file to table

Corrected working routine:

Open original

SaveAs to Temp dir  <~~the SaveAs is the key.

Close Obj

Quit Obj

Transferspreadsheet Copied file to table

The SaveAs works because the links update from the original file when it opens (not marked as an untrusted location or file) and then SaveAs saves the original file to the TEMP location with the updated links. 

If I am wrong about something, let me know.  Hopes this helps someone.