Problems with DoCmd.TransferSpreadsheet

[nasa09]

Hi everyone. I've recently started dipping my toes into modules and macros in Access, so please be gentle with me.

I have a form where the user enters a begin date and and end date and then pushes a button. The button runs a query and is then supposed to export data to an unformatted .xlsx file. The problem I'm having is that the exported file, while having the .xlsx file extension, won't open unless I edit the file name to change the extension to .xls. The code for the module is below, and it looks right to me, but again, this is new territory for me.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "iLienRed Upload (OSI)", "G:\Private\LOANSERV\Reconveyance\MDPS Files\Completed MDPS Uploads\iLienRed Upload - OSI - " & Format(Forms![frm_ilien_red_spreadsheet]![txtEndDt], "YYYY-MM-DD") & ".RAW.xlsx", True

Any help that you great wise ones could provide would be deeply appreciated!

 

[MSAccessRookie]

Hi everyone. I've recently started dipping my toes into modules and macros in Access, so please be gentle with me.

I have a form where the user enters a begin date and and end date and then pushes a button. The button runs a query and is then supposed to export data to an unformatted .xlsx file. The problem I'm having is that the exported file, while having the .xlsx file extension, won't open unless I edit the file name to change the extension to .xls. The code for the module is below, and it looks right to me, but again, this is new territory for me.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "iLienRed Upload (OSI)", "G:\Private\LOANSERV\Reconveyance\MDPS Files\Completed MDPS Uploads\iLienRed Upload - OSI - " & Format(Forms![frm_ilien_red_spreadsheet]![txtEndDt], "YYYY-MM-DD") & ".RAW.xlsx", True

Any help that you great wise ones could provide would be deeply appreciated!

You did not specify the nature of your error(s), but it is known that DoCmd.TransferSpreadsheet can have trouble under Access 2010 when there is a hidden worksheet that is "seen" as the first worksheet. This condition previously was ignored by Access 2003 and before.

If you think that this could be related to your issues, then see http://www.eileenslounge.com/viewtopic.php?f=27&t=11950 for additional details.

-- Rookie

 

[vbaInet]

It doesn't need to be told which extension to use.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12[COLOR="blue"]Xml[/COLOR], "iLienRed Upload (OSI)", "G:\Private\LOANSERV\Reconveyance\MDPS Files\Completed MDPS Uploads\iLienRed Upload - OSI - " & Format(Forms![frm_ilien_red_spreadsheet]![txtEndDt], "YYYY-MM-DD") & "[COLOR="Blue"].RAW[/COLOR]", True

By the way, welcome to AWF!

 

[nasa09]

Thanks a lot for your help and welcoming spirit, guys!

vbaInet's solution was ultimately what led to success. While removing the file extension resulted in more errors, changing acSpreadsheetTypeExcel12 to acSpreadsheetTypeExcel12Xml did the trick!