Microsoft excel Versions

[TimTDP]

I need to export data into various versions of Excel. The user needs to select the correct version.

I have created a combo box and set the row source to:
"14; Excel 2010; xlsx;12; Excel 2007; xlsx;11; Excel 2003; xls;10; Excel 2002; xls;9; Excel 2000; xls;8; Excel 97; xls;7; Excel 95; xls"

This provides a combo box with 3 columns.
1st column = Excel Version for vba code
2nd column = Excel Version for non technical users
3rd column = File extension

I am experiencing the following problems when I run the code:
DoCmd.TransferSpreadsheet acExport, 14, "qrySupplierQuantity", "C:\Temp\Data.xlsx"

I get an error 2508 - spread sheet type invalid

DoCmd.TransferSpreadsheet acExport, 9, "qrySupplierQuantity", "C:\Temp\Data.xls"

I cannot open the file! I get an error message that I am trying to open a file that is in a different format to the file extension.

How do I correct these errors?

Many thanks in advance

 

[Trevor G]

Tim,

I use the following to export a query and as it is using the Object Command you don't need to worry about the version it places the query into a new workbook. You would need to set the References to use DAO in the VBA screen select the Tools Menu and then Reference to check if DAO has been selected if not then search down the list for Microsoft DAO and check the box.

Sub trans1()
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer

'Step 2: Identify the database and query
Set MyDatabase = CurrentDb
Set MyQueryDef = MyDatabase.QueryDefs("qryAverageNumberRoomType") 'Query name in the database

''Step 3: Define the Parameters
'With MyQueryDef
'.Parameters("[Enter Department]") = Range("D3").Value 'From parameter field in access
'
'End With

'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset

'Step 5: Clear previous contents
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.workbooks.Add
.Sheets("Sheet1").Select
.ActiveSheet.Range("A6:K10000").ClearContents

'Step 6: Copy the recordset to Excel
.ActiveSheet.Range("A7").CopyFromRecordset MyRecordset

'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
xlApp.ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
Next i
xlApp.Cells.EntireColumn.AutoFit
End With

MsgBox "Query has been successful", vbInformation, "Sample"
End Sub

 

[TimTDP]

Thanks, this can work but I still need to save the Excel spreadsheet in a format the user can read. They have Excel 2000 and I have Excel 2010

I also get an error when opening a query that refers to a control on a form. No records are returned in line:
Set MyQueryDef = MyDatabase.QueryDefs("qrySupplierOrderExcelCodeQuantity")
The query returns the correct results in a report, so I can assume the query works!

 

[gemma-the-husky]

i would export to excel97 format - then anything can read it
just use this instead of your numeric constants. this will just generate a basic .xls file

acSpreadsheetTypeExcel97


alternatively, prodcue a csv with transfertext. it's probably more powerful than transferspreadsheet, anyway.