Error 91 driving me nuts

[cheekybuddha]

I just noticed you have another ActiveWorkbook reference that is unqualified.

You should really try and replace them all with a proper object variable

(Edit: just saw @arnelgp mentioned this)

 

[cheekybuddha]

Sorry, answering from phone previously.

You will end up with orphan instances of Excel in your Task Manager any time you have an unqualified reference to an Excel object in your automation code.

 

[TonyFried]

Thanks to everybody who contributed. Yep, I know (have always been aware) that the error handling is non-existent (I'm not good at that - untidy me). In the end a combination of Minty, DocMan and cheekybuddah's suggestions seems to have done the trick.
In answer to Minty (last week) what I'm doing is updating client portfolios to reflect changes in the master database (used to produce said portfolios) in response to market changes (we are in the financial industry). So - cycle through the client workbook and update the Access app, open a master worksheet and and save to new file. a clumsy way of working necessitated by "orders from above".
anyway, the code now looks like this:

Dim db              As DAO.Database
Dim qdf             As DAO.QueryDef
Dim prm             As DAO.Parameter
Dim lngRowsAffected As Long
Dim lngRowsDeleted As Long
Dim fileName As Variant
Dim fsname As Variant
Dim xlApp As Excel.Application
Set db = CurrentDb
fileName = Dir("\\Svrfiles\access databases\Portfolio Test\")
Do While fileName <> ""
 DoCmd.OpenQuery "QryDeleteCash", acViewNormal
    DoCmd.TransferSpreadsheet acLink, , "TblInputSht", "\\Svrfiles\access databases\Portfolio Test\" & fileName, False, "Input Sheet!D6:E32"
    DoCmd.TransferSpreadsheet acLink, , "TblInputShtName", "\\Svrfiles\access databases\Portfolio Test\" & fileName, False, "Input Sheet!E3:E3"
    Set qdf = db.QueryDefs("QryImport2")
    db.Execute "QryImport2", dbFailOnError
        For Each prm In qdf.Parameters
            prm = Eval(prm.Name)
        Next prm
 
    qdf.Execute
    lngRowsAffected = db.RecordsAffected
    DoCmd.DeleteObject acTable, "TblInputShtName"
    DoCmd.DeleteObject acTable, "TblInputSht"
    Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True
        xlApp.Workbooks.Open "\\Svrfiles\ACCESS DATABASES\cashrptgraph.xlsm", True, False
        xlApp.ActiveWorkbook.RefreshAll
            TWait = Time
            TWait = DateAdd("s", 10, TWait)
                Do Until TNow >= TWait
                    TNow = Time
                Loop
    fsname = xlApp.Application.GetSaveAsFilename
    xlApp.ActiveWorkbook.SaveAs fileName:=fsname
    xlApp.ActiveWorkbook.Close
        fileName = Dir
        If fileName <> "" Then
            DoCmd.OpenQuery "QryDeleteCash", acViewNormal
        End If
Loop
    xlApp.Quit
    Set xlApp = Nothing

I occasionally get a "remote call failed" error when saving, but that is probably related to me working remotely (I hope)

 

[cheekybuddha]

Hi,

Great you seem to have a solution!

Just wondering about this bit:

' ...
    Set qdf = db.QueryDefs("QryImport2")
    db.Execute "QryImport2", dbFailOnError
        For Each prm In qdf.Parameters
            prm = Eval(prm.Name)
        Next prm
 
    qdf.Execute
' ...

It looks like your executing the same query twice. Is that intentional?

What is the SQL of QryImport2 ?

 

[TonyFried]

If I am, that would be my inept coding and certainly not deliberate . SQL is

INSERT INTO TblHolderCash ( FUND, Amount, Client )
SELECT DISTINCT TblInputSht.F1 AS FUND, TblInputSht.F2 AS AMOUNT, TblInputShtName.F1 AS Name
FROM TblInputSht, TblInputShtName
WHERE ((([TblInputSht].[F2]) Is Not Null));

 

[cheekybuddha]

OK, so no parameters need resolving. You can probably lose the qdf and prm object variables, and refactor like:

Dim db              As DAO.Database
Dim lngRowsAffected As Long
Dim lngRowsDeleted  As Long
Dim fileName        As Variant
Dim fsname          As Variant
Dim xlApp           As Excel.Application

Set db = CurrentDb
fileName = Dir("\\Svrfiles\access databases\Portfolio Test\")
Do While fileName <> ""
  DoCmd.OpenQuery "QryDeleteCash", acViewNormal       ' this can probably be: db.Execute "QryDeleteCash", dbFailOnError
  DoCmd.TransferSpreadsheet acLink, , "TblInputSht", "\\Svrfiles\access databases\Portfolio Test\" & fileName, False, "Input Sheet!D6:E32"
  DoCmd.TransferSpreadsheet acLink, , "TblInputShtName", "\\Svrfiles\access databases\Portfolio Test\" & fileName, False, "Input Sheet!E3:E3"
  db.Execute "QryImport2", dbFailOnError
  lngRowsAffected = db.RecordsAffected
  DoCmd.DeleteObject acTable, "TblInputShtName"
  DoCmd.DeleteObject acTable, "TblInputSht"
  Set xlApp = CreateObject("Excel.Application")
  With xlApp
    .Visible = True
    With .Workbooks.Open "\\Svrfiles\ACCESS DATABASES\cashrptgraph.xlsm", True, False
      .RefreshAll
      DoEvents
      ' The following 10 second delay seems quite long - perhaps DoEvents will take care of it?
      TWait = Time
      TWait = DateAdd("s", 10, TWait)
      Do Until TNow >= TWait
        TNow = Time
      Loop
      fsname = xlApp.Application.GetSaveAsFilename
      .SaveAs fileName:=fsname
      .Close
    End With
    fileName = Dir
    ' The following is not necessary as it happens at the beginning of the loop
    ' If fileName <> "" Then
    '   DoCmd.OpenQuery "QryDeleteCash", acViewNormal
    ' End If
  End With
Loop
xlApp.Quit
Set xlApp = Nothing
Set db = Nothing

I have made some other notes in the code too

 

[TonyFried]

Grateful thanks - potentially a lot tidier. will have a look later (one step at a time!!!)

 

[cheekybuddha]

One other thing to look at later:

' ...
fileName = Dir("\\Svrfiles\access databases\Portfolio Test\")
' ...

This will look at every file in that folder. If there are any non-Excel files it will probably throw an error.

You may wish to change to something like:

' ...
fileName = Dir("\\Svrfiles\access databases\Portfolio Test\*.xls*")
' ...

And also just noticed that you are creating the xlApp object on each iteration of the loop. Move the following line

  Set xlApp = CreateObject("Excel.Application")

outside the loop, eg after line:

' ...
Set db = CurrentDb
Set xlApp = CreateObject("Excel.Application")
' ...

hth,

d

 

[TonyFried]

cheekybuddah - definitely NOT a navel-gazer! Yep - all those worked with a couple of riders:
1.

With .Workbooks.Open "\\Svrfiles\ACCESS DATABASES\cashrptgraph.xlsm", True, False 'needed to remove the with'

      .RefreshAll 'needed to add .activeworkbook'

2. Needed to keep the wait loop otherwise the save dialog started before the refresh had finished, but will reduce the time until I hit the limit!
Have also discovered that the directory the files are saved to must not be open in, e.g. Windows File Explorer, or I get the remote call failed error - now THATS an error I need to trap!

 

[Minty]

You may find that your error is due to having the file preview switched on in explorer, which effectively "opens" the file to give you the preview.
Very annoying that.

 

[TonyFried]

As are so many things microsoftish

 

[cheekybuddha]

Hi,

Yes, sorry, I forgot the required parentheses.

The Workbooks.Open() method is actually a function that returns a Workbook object, but to use it as such it's parameters must be passed within parentheses, otherwise it behaves like a sub (ie discards its return object).

The code should have read:

' ...
  With xlApp
    .Visible = True
    With .Workbooks.Open("\\Svrfiles\ACCESS DATABASES\cashrptgraph.xlsm", True, False)
      .RefreshAll[
' ...

This way you now have a reference to the Workbook object within your With ... End With block and no longer need to use the Application.ActiveWorkbook object which is best avoided.

However, if what you have works, then perhaps better stick with it.