TransferSpreadsheet in Access (Excel Not Closing)

T

ThunderTek

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
 
K

Ken Snell \(MVP\)

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

ThunderTek

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?
 
K

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?
 
T

ThunderTek

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.
 
K

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top