Error Occurring Trying to Close Workbook Exported with Access VBA

Question

I'm trying to write code to have Access close a spreadsheet in Excel that Access just exported. However, I keep throwing an error that tells me my table does not exist even though I'm STARING STRAIGHT AT IT! Very frustrating. All help is greatly appreciated. Thanks.

Private Sub Command12_Click()

DoCmd.SetWarnings False

Const xlFileName As String = "\\ct13nt003\mfg\SMT_Schedule_Files\SMT Line Progress Files\Test\SMT2Updated.xlsx"
Dim xlapp As Object
Dim wb As Object

Set xlapp = CreateObject("Excel.Application")
Set wb = xlapp.workbooks.Open(xlFileName)

'Delete Existing File First; then create new
On Error Resume Next
Kill xlFileName
On Error GoTo 0

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SMT4Export", xlFileName, True

wb.Close savechanges:=False

Set wb = Nothing
Set xlapp = Nothing

DoCmd.SetWarnings True

End Sub

It states - "Run-time error '3001' - The Microsoft Office Access database engine could not find the object 'SMT4Export'. Make sure the object exists and that you spell its name and the path name correctly." — ackdaddy, Nov 22 '13 at 19:10
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SMT4Export", xlFileName, True' is the line that is highlighted. — ackdaddy, Nov 22 '13 at 19:11
Why are you creating an instance of Excel, opening the file, and *then* trying to delete the file while it is open? In fact, why are you creating an instance of Excel at all? — Gord Thompson, Nov 22 '13 at 19:18
Please don't add important information in the comments. You should [edit] your question and add it there, where people can see it when reading your question (and where it can be found in a search by future readers). Also, there is never an excuse for saying "I get an error" without including the **exact error** that you're getting, including any error codes, error message, or compiler error. It's on your screen right in front of *you*, but we can't see your screen from where we're sitting. If you want help, give us the information you have available to use to try and help you. — Ken White, Nov 22 '13 at 22:56

HansUp · Accepted Answer · 2013-11-22T21:10:17.550

1

You open xlFileName in Excel. Then you attempt Kill xlFileName while that workbook file is still open in Excel. So the Kill attempt fails, but you are unaware of that fact because you suppressed the error message by using On Error Resume Next.

You need to close the workbook in Excel to release the lock if you want to Kill it. On the other hand, as Gord already mentioned, there is no indication in your code as to why it's useful to open the workbook in Excel in the first place.

Regarding the error message "Run-time error '3001' - The Microsoft Office Access database engine could not find the object 'SMT4Export'. Make sure the object exists ...", make sure your database includes a table or query named SMT4Export.

It seems TransferSpreadsheet is the goal. I suggest you avoid creating an Excel application instance for now and see whether you can get this simplified version of your code to work ...

Private Sub Command12_Click()
    Const xlFileName As String = "\\ct13nt003\mfg\SMT_Schedule_Files\SMT Line Progress Files\Test\SMT2Updated.xlsx"

    DoCmd.SetWarnings True

    If Len(Dir(xlFileName)) > 0 Then
        Kill xlFileName
    End If

    DoCmd.TransferSpreadsheet acExport, _
        acSpreadsheetTypeExcel12Xml, "SMT4Export", xlFileName, True
End Sub

edited Nov 22 '13 at 21:10

answered Nov 22 '13 at 19:22

HansUp

95,961
11
77
135

I am trying to kill the file "SMT2Updated.xlsx" which already exists, then export the table "SMT4Export", which does exist in my database, to a file named "SMTUpdated.xlsx" and then close the new file. That's my real problem is that I cannot get it to export and then close. – ackdaddy Nov 22 '13 at 20:55
What is the point of opening *SMT2Updated.xlsx* in Excel before you attempt `TransferSpreadsheet`? – HansUp Nov 22 '13 at 21:01
There isn't one, I forgot I had that line of code in there. I removed it, but I'm still getting errors. I'm obviously a novice, so I'm trying to plunk my way through unfortunately. – ackdaddy Nov 22 '13 at 21:03
Please test the `Command12_Click` version from my answer. If you get an error, give us the error message and indicate which line triggers the error. If no error, but it doesn't do what you need, please explain what more you need from it. – HansUp Nov 22 '13 at 21:11
It worked!!! So what in that line of code caused the file to not open? Or to close? I do not see a close command. – ackdaddy Nov 22 '13 at 21:19

Error Occurring Trying to Close Workbook Exported with Access VBA

1 Answers1