TransferSpreadsheet with 1) space names in worksheet name & 2) in .xlsx format

[RobH2014]

Background: Trying to automate an existing process using Access 2010.

I'm trying to do something really simple, output some queries into .xlsx format. However I can't get it to work properly (code below). I have two separate problems.

1) I need to export in .xlsx format. However when I use acSpreadsheetTypeExcel12 Access will create the Excel file, but it is corrupted. When you try and open it in Excel you get a message saying 'Excel cannot open file <FILENAME> because the file format of file extension is not valid'. If I use acSpreadsheetTypeExcel11 or acSpreadsheetTypeExcel10 I get a Access runtime error 3170 'Could not find installable ISAM'.
Currently I am using acSpreadsheetTypeExcel9 and then resaving the excel file manually as .xlsx. Is there any way to export as .xlsx from Access?

2) I need to output some excels worksheets that have spaces in the worksheet name (this is a user defined property not mine). However the transferspreadsheet command doesn't seem to like this, and instead will replace spaces in the sheet name with '_'. Is there a way of getting Access to allow spaces in Excel sheet names?

Can anyone help with this?

Function QueryExports()

Dim str_dir As String
Dim str_filepath_WF_IP As String
Dim str_filepath_WF_AE As String
Dim str_reportmonth As String
Dim str_date As String

'Get report month
str_reportmonth = InputBox("Please enter the current report month in CAPS", "Get reporting month")
If str_reportmonth = "" Then
    MsgBox "No value specified, code stopped"
    Exit Function
End If

'Get current filepath
str_dir = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))

'Get current date
str_date = Format(Date, "yyyy.mm.dd")

str_filepath_WF_IP = str_dir & "FFT_NHSE_IP_" & str_reportmonth & "_" & str_date & ".xlsx"
str_filepath_WF_AE = str_dir & "FFT_NHSE_AE_" & str_reportmonth & "_" & str_date & ".xlsx"

'Create IP Webfile output
Call ExportToXLSX("821_NHSE_IP_Ward", str_filepath_WF_IP, "IP Ward")
Call ExportToXLSX("822_NHSE_IP_Site", str_filepath_WF_IP, "IP Site")
Call ExportToXLSX("823_NHSE_IP_Trust", str_filepath_WF_IP, "IP Trust") 
'Create AE Webfile output
Call ExportToXLSX("824_NHSE_AE_Site", str_filepath_WF_AE, "AE Site")
Call ExportToXLSX("825_NHSE_AE_Org", str_filepath_WF_AE, "AE Trust")

End function

Private Sub ExportToXLSX(str_query As String, str_filepath As String, str_sheetname As String)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, str_query, str_filepath, True, str_sheetname
End Sub

[NTC]

in reverse order

2) the column names of the exported excel should be identical to the record source being exported. presuming that record source is a query, or can be a query, you can manipulate column name displays simply with alias/calculated field approach; i.e. Last Name: last_name

1) Start from scratch and do a manual export; but this I mean run your query, then using the ribbon go to External Data and do the export to excel - - in this process it is key to see if the xlsx format is available to you. If so - use it - and then the wizard will end with a prompt to save the export - do that, give it a name, and then use that saved export for your requirement. If it does not offer the xlsx format then your issue isn't an Access issue but rather a more generic Office or possibly OS issue and your best bet for aid is to go into Microsoft's Office/OS support user forum. If you do end up going into Microsoft support forum - keep your description limited to this manual export and why is xlsx not being offered as this is pure generic Microsoft - and the fix to that should be the fix overall.

[RobH2014]

in reverse order

2) the column names of the exported excel should be identical to the record source being exported. presuming that record source is a query, or can be a query, you can manipulate column name displays simply with alias/calculated field approach; i.e. Last Name: last_name

1) Start from scratch and do a manual export; but this I mean run your query, then using the ribbon go to External Data and do the export to excel - - in this process it is key to see if the xlsx format is available to you. If so - use it - and then the wizard will end with a prompt to save the export - do that, give it a name, and then use that saved export for your requirement. If it does not offer the xlsx format then your issue isn't an Access issue but rather a more generic Office or possibly OS issue and your best bet for aid is to go into Microsoft's Office/OS support user forum. If you do end up going into Microsoft support forum - keep your description limited to this manual export and why is xlsx not being offered as this is pure generic Microsoft - and the fix to that should be the fix overall.

Hi, thanks for your response
2) It's the worksheet names, not the column headers, that need to have spaces in them. So changing field names in Access won't have any effect.

1) The export .xlsx works OK as a saved export, however that option doesn't give me the flexibility I need in terms of specifying sheet names and exporting multiple queries to the same workbook. I couldn't find any code for the saved export which I could edit either.

It may be irrelevant now as we appear to be moving the process from Access to Excel, but it would be interesting to know if there was a solution.

[lfpm062010]

If you change the spreadsheet type from "acSpreadsheetTypeExcel12" to "acSpreadsheetTypeExcel12Xml", then it will work.

Private Sub ExportToXLSX(str_query As String, str_filepath As String, str_sheetname As String)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, str_query, str_filepath, True, str_sheetname
End Sub

[ranman256]

If you change the spreadsheet type from "acSpreadsheetTypeExcel12" to "acSpreadsheetTypeExcel12Xml", then it will work.

Private Sub ExportToXLSX(str_query As String, str_filepath As String, str_sheetname As String)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, str_query, str_filepath, True, str_sheetname
End Sub

I too have built a series of functions for this. All run via Macro. They ALL write to the same XL file output.xls

macro1
---
killStdFile (erases the output.xls so we always start fresh)
MakeXLrpt qry, sheet
MakeXLrpt qry, sheet
MakeXLrpt qry, sheet
OpenStdFile (to open 1 book, all sheets)
--- end macro


Public Function MakeXlRpt(ByVal pvQry, ByVal pvSht)
Dim vFile
On Error Resume Next
vFile = getStdOutfile()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, pvQry, vFile, True, pvSht
End Function