Create new Spreadsheet

[Vassago]

I am trying to use the following code to export a table into a new spreadsheet that does not exist yet:

DoCmd.TransferSpreadsheet acExport, tbl_temp, "C:\Spreadsheets\" & rst!clientid & ".xls", True

but it won't work because the spreadsheet doesn't exist. How can I make Access create the spreadsheet?

Thanks in advance!

Vassago

 

[Vassago]

Nevermind, I have solved the problem using the Output to method.

 

[Uncle Gizmo]

I use the following code which I adapted from Microsoft. Links to Microsoft are included in the code. You will also need the other function below to generate the Path.

Just change the line below "Put your own SQL here" any text SQL statement that works can be used.

Private Sub fMakeSpreadsheet()

'http://support.microsoft.com/default.aspx?scid=kb;en-us;246335
'http://support.microsoft.com/default.aspx?scid=kb;EN-US;247412

Dim adoCon As New ADODB.Connection
Dim adoRst As New ADODB.Recordset

Set adoCon = CurrentProject.Connection
   'Put your own SQL here
adoRst.Open "SELECT * FROM tblCustomer", adoCon
    
   'Create a new workbook in Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   Set oSheet = oBook.Worksheets(1)

   'Transfer the data to Excel
   
   'Add General Info to spreadsheet
   
   oSheet.Range("A1") = "These records were Queried on, Date :  " & FormatDateTime(Date, vbLongDate) & ", Time:  " & FormatDateTime(Now, vbShortTime)
   oSheet.Range("A2") = "By Tony Hine Tel +44 (0) 1635 522233  Email:   mail@tonyhine.co.uk"
   oSheet.Range("A3") = "I am in Ecademy you Can find me at: http://www.ecademy.com/account.php?id=94555"
   oSheet.Range("A4") = "I post here mostly: http://www.access-programmers.co.uk/forums/"
   oSheet.Range("A5") = "The query specified was "
   oSheet.Range("A6") = ""
   oSheet.Range("A7") = "FROM COMBO BOX"
   oSheet.Range("A8") = ""
   
   'Add Columb Headings
   oSheet.Range("A9") = "Batch No"
   oSheet.Range("B9") = "Serial No"
   oSheet.Range("C9") = "Flexi Change"
   oSheet.Range("D9") = "PCB Change"
   oSheet.Range("E9") = "Customer"
   
   'Add The Data
   oSheet.Range("A10").CopyFromRecordset adoRst

   'Save the Workbook and Quit Excel
   oBook.SaveAs fPathFileName
   oExcel.Quit

   MsgBox "You have successfully saved a spreadsheet containing this data to your local C:\ drive.  " & _
   "If you look in your C:\ drive you will see a file named: " & fPathFileName

'Close the connection
    adoRst.Close
    adoCon.Close
   
End Sub      ' fMakeSpreadsheet()

This function just gives you a date stamped file name.

Private Function fPathFileName()
Dim strYear As String
Dim strMth As String
Dim strDay As String
Dim strPath As String
Dim strFileName As String
Dim strFileXtn As String

strYear = DatePart("yyyy", Date)
strMth = DatePart("m", Date)
strDay = DatePart("d", Date)

strPath = "C:\"
strFileName = "YourFile"
strFileXtn = ".xls"

    fPathFileName = strPath & strFileName & strYear & "_" & strMth & "_" & strDay & strFileXtn
End Function      '   fPathFileName()

 

[Bat17]

looks like you had a comma missing.
DoCmd.TransferSpreadsheet acExport, , tbl_temp, "C:\Spreadsheets\" & rst!clientid & ".xls", True

Access will create a spreadsheet if it does not exist.

Peter