I must be missing soemthing obvious! (1 Viewer)

Hi all,

Last year I developed an access module that allowed me to open, refresh connections, save and close an excel file. I need to do the same again, with almost identical projects / workbooks, so thought it would be a simple case of copying and pasting the old access file, renaming it and changing the code to the new file. I have done this, and all documents are saved in the same location, but its not working.

Here is the code that worked, and still works on the old excel file:

Function fRefreshWorkbook() As Boolean
On Error GoTo Err_fRefreshWorkbook
Dim objXL As Object, objWbk As Object, blNewInst As Boolean, _
strPathToFile As String
Const c_strFile As String = "Document.xlsx"
On Error Resume Next
' See if we can grab an instance of Excel already running
Set objXL = GetObject(, "Excel.Application")
If Err <> 0 Then
' No existing instance of Excel, let's create one
Set objXL = CreateObject("Excel.Application")
' Set a flag so we know to destroy the instance after we're done
blNewInst = True
Err = 0
End If
On Error GoTo Err_fRefreshWorkbook
' Define the Excel file we want to open
strPathToFile = CurrentProject.Path & "" & c_strFile
' Open the file setting the parameter to RefreshLinks = True
Set objWbk = objXL.Workbooks.Open(strPathToFile, True)
With objWbk
.Connections("ECWL Report").Refresh
.Connections("ECWL Report1").Refresh
.Save
.Close
End With
Exit_fRefreshWorkbook:
If Not objWbk Is Nothing Then Set objWbk = Nothing
If Not objXL Is Nothing Then
If blNewInst Then objXL.Quit
Set objXL = Nothing
End If
Exit Function
Err_fRefreshWorkbook:
Select Case Err.Number
Case Else
MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
"Description: " & Err.Description & vbNewLine & vbNewLine & _
"Procedure: fRefreshWorkbook" & vbNewLine & _
IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
"Module: basTest", , "Error: " & Err.Number
End Select
Resume Exit_fRefreshWorkbook
End Function

I thought it would be a simple case of renaming the Const c_strFile As String = "Document.xlsx" to "Document1.xlsx"

The old code still works perfectly and does the job required.

However, when I try the above on the new files I get the error:

Error No.: 9
Description: Subscript out of range
Procedure: fRefreshWorkbook
Module: basTest

Many thanks in advance for any suggestions!

if you comment out the error handling line
On Error GoTo Err_fRefreshWorkbook

You'll see which line in the code it doesn't like when you hit debug. That will either make it obvious to you or if not help us to see where and possibly what the issue is.

Thanks Minty!

That has resolved the Error No.9 issue, but it is now asking me to save the updated file, as if when the excel file is opening it is in read only format perhaps? Is there a way around this?

As well, normally when the module was run, a window would pop up saying "Downloading" and a green bar indicating progress...it would then have a think and then a similar screen would pop up saying "Saving As" and then disappearing when completed. This hasn't happened either (obviously not the later bit)

Any thoughts on why this might not be happening as previous?

The next thing I would try would be to manually open the new file with Excel and see what it says. If there is a file error in an Excel App object, there should be a file error in Excel as the main app. If so, you can address the error via Excel. If not, then you have at least isolated it to something about app objects.

Hi The_Doc_Man

Thanks for your reply - I have opened the new excel file and it opens fine, not in read only mode and saves fine without any problems, so that doesn't seem to be the problem

Try
objwbk.close , False

I am still having problems with the excel file opening in read only. Is this because the table I am opening is a linked table in the access program and therefore already active or open.

Is there a way I could break the link, or perhaps change the module to close the access project to make the excel link inactive, run the above code to refresh the links, then open the access project again?

This sounds very familiar to a issue I had encountered a while back.

If I remember correctly, the Excel file can be linked in the Access database and can be open in read/write in Excel, but only if the linked file is not "open" in Access. Can't be in an open query, open table, form or report. If Access does have the file open, then Access has the lock on the file and any other application (read: Excel) can only have it in read only.

Not sure if this is your issue as you haven't mentioned if Access has the file open or not at the time of your procedure running. Possible solution is to close whatever Access object(s) have the Excel file open, run your procedure, then reopen the Access objects (if necessary).