TransferSpreadsheet in Access (Excel Not Closing)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ThunderTek - 16 Jan 2006 09:19 GMT

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

Reply to this Message

Ken Snell (MVP) - 16 Jan 2006 14:06 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

>I need to quantify the TransferSpreadsheet line in my code. Please Help
>
[quoted text clipped - 11 lines]
> objXL.Quit
> Set objXL = Nothing

Reply to this Message

ThunderTek - 17 Jan 2006 14:43 GMT

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?

> Do not open the file to which you're transferring the data. Just use
> TransferSpreadsheet to put data in the file.
[quoted text clipped - 14 lines]
> > objXL.Quit
> > Set objXL = Nothing

Reply to this Message

Ken Snell (MVP) - 17 Jan 2006 17:16 GMT

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?

Signature

Ken Snell
<MS ACCESS MVP>

> 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,
[quoted text clipped - 22 lines]
>> > objXL.Quit
>> > Set objXL = Nothing

Reply to this Message

ThunderTek - 17 Jan 2006 18:49 GMT

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.

> I am not aware of any such add-in, no.
>
[quoted text clipped - 27 lines]
> >> > objXL.Quit
> >> > Set objXL = Nothing

Reply to this Message

Ken Snell (MVP) - 17 Jan 2006 21:51 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

> 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
[quoted text clipped - 36 lines]
>> >> > objXL.Quit
>> >> > Set objXL = Nothing

Reply to this Message