Unexpected results when exporting to spreadsheet

[SimonG via AccessMonster.com]

Trying to export Access query to an Excel spreadsheet.

Ultimately several querys will be exported, each to its own worksheet within
a single workbook.
The workbook does not need to be opened, as no user intervention is required
(eventually the workbook will be used as an import for another program).

Currently the "export" routine is importing from the spreadsheet, not
exporting to it!

I have tried: running the routine where the spreadsheet exists, spreadsheet
is importing (from target worksheet/fields matched correctly); running the
routine where no spreadsheet exists, no output is produced. No error
messages occur during either run.

Reading from several books and around the web, the code I have appears in-
line with my reading.

Could someone please look over the code and suggest what I might be
missing/need to change?

Export code:
DoCmd.TransferSpreadsheet asExport, _
acSpreadsheetTypeExcel7, _
strTable, _
strWorksheetPath, _
True, _
strWorksheetName

Routine is in Access 2007 on Vista Pro.

Many thanks,
Simon

 

[Klatuu]

Your problem is here
-----------v
DoCmd.TransferSpreadsheet asExport

It should be acExport

The options you use are intrinsic contstants. the value of acExport is 1
The value of asExport would be vbNullString or ""
Because your code is seeing a null string in the transfer type argument, it
is using the default which is to import. So the immediate solution is to
correct the misspelling.
The real issue here is you are not using Option Explicit in your code
modules. If you were, that misspelling would have thrown an error when you
tried to compile your application and you would have save yourself a lot of
grief.

 

[SimonG via AccessMonster.com]

Thank you Klatuu!

I've been reading around trying to track that down since last week(!); such
an unexpected typo, don't know I ever would have spotted it.

Also thank you for the Option Explicit tip. I've now read up on this, and
discovered checking 'Require Variable Declaration' within Tools / Options
automatically adds Option Explicit to new modules.

Thanks once again,
Simon

Your problem is here
-----------v
DoCmd.TransferSpreadsheet asExport

It should be acExport

The options you use are intrinsic contstants. the value of acExport is 1
The value of asExport would be vbNullString or ""
Because your code is seeing a null string in the transfer type argument, it
is using the default which is to import. So the immediate solution is to
correct the misspelling.
The real issue here is you are not using Option Explicit in your code
modules. If you were, that misspelling would have thrown an error when you
tried to compile your application and you would have save yourself a lot of
grief.

Trying to export Access query to an Excel spreadsheet.

[quoted text clipped - 29 lines]

Many thanks,
Simon

 

[Klatuu]

Yes, I should have mentioned that. It really is a good idea to always have
that checked.
Were I you, I would open each module including any form and report modules
and put it in then compile my app to be sure everything is as it should be.
--
Dave Hargis, Microsoft Access MVP

Thank you Klatuu!

I've been reading around trying to track that down since last week(!); such
an unexpected typo, don't know I ever would have spotted it.

Also thank you for the Option Explicit tip. I've now read up on this, and
discovered checking 'Require Variable Declaration' within Tools / Options
automatically adds Option Explicit to new modules.

Thanks once again,
Simon

Your problem is here
-----------v
DoCmd.TransferSpreadsheet asExport

It should be acExport

The options you use are intrinsic contstants. the value of acExport is 1
The value of asExport would be vbNullString or ""
Because your code is seeing a null string in the transfer type argument, it
is using the default which is to import. So the immediate solution is to
correct the misspelling.
The real issue here is you are not using Option Explicit in your code
modules. If you were, that misspelling would have thrown an error when you
tried to compile your application and you would have save yourself a lot of
grief.

Trying to export Access query to an Excel spreadsheet.

[quoted text clipped - 29 lines]

Many thanks,
Simon