TransferSpreadsheet in Access (Excel Not Closing)

[Guest]

I need to quantify the TransferSpreadsheet line in my code. Please Help

Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
objXL.Workbooks.Open (objXL.LibraryPath & "\AtData.XLA") '.RunAutoMacros 1
objXL.Workbooks.Open "C:\AlkyLabData.xls"
objXL.Visible = False

DoCmd.TransferSpreadsheet , , "TestingImportData2", "C:\AlkyLabData.xls", True

Me.Requery
DoCmd.GoToRecord , , acLast
objXL.Quit
Set objXL = Nothing

 

[Ken Snell \(MVP\)]

Do not open the file to which you're transferring the data. Just use
TransferSpreadsheet to put data in the file.

 

[Guest]

The file that I am attempting to open must calculate to receive new data
before it is imported to Access. Can an Excel file Addin be installed, the
file then be calculated and imported all without actually opening the file?

 

[Ken Snell \(MVP\)]

I am not aware of any such add-in, no.

Will the file provide the data you need if you open the file, let it
"recalculate", save and close it, and then do the TransferSpreadsheet?

 

[Guest]

The Addin allows Excel to receive information from a program called Aspen
Tech (used in importing Lab Data). I need this data to update before
importing to Access. So, can Excel recalc without opening? If so, I think
that would solve my problem. Otherwise I need to quantify that DoCmd line.

 

[Ken Snell \(MVP\)]

I don't know of a way to have the EXCEL file recalculate while it's closed.

Try saving and then closing the file before you do the TransferSpreadsheet
action:


Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
objXL.Workbooks.Open (objXL.LibraryPath & "\AtData.XLA") '.RunAutoMacros 1
objXL.Workbooks.Open "C:\AlkyLabData.xls"

objXL.Workbooks("C:\AlkyLabData.xls").Save
objXL.Workbooks("C:\AlkyLabData.xls").Close
DoEvents

objXL.Visible = False

DoCmd.TransferSpreadsheet , , "TestingImportData2", "C:\AlkyLabData.xls",
True

Me.Requery
DoCmd.GoToRecord , , acLast
objXL.Quit
Set objXL = Nothing