Export form from Access to Excel in one workbook

[wbarker]

With the following code, i was able to export data from access to a preexisting spreadsheet, to the desired tab...on the same worksheet.

rivate Sub Image15_Click()


Dim xl As Object
'Step 1: Start Excel, then open the target workbook.
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("\\synthes\O-drive\West Chester\Product Data Management\Forecast\IRB Summary\IRB Summary MMYYYY.xlsm")
'Step 2: Make Excel visible
xl.Visible = True

'Step 3: Run the target macro
xl.Run "clear_all_worksheets"

'Step 4: Close and save the workbook, then close Excel
xl.ActiveWorkbook.Close (True)
xl.Quit

'Step 5: Memory Clean up.
Set xl = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
"AWSL On Hand Value All Suppliers by Co", "\\synthes\O-drive\West Chester\Product Data Management\Forecast\IRB Summary\IRB Summary MMYYYY.xlsm", True, "AWSL On Hand"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
"BP24 Returns by date range", "\\synthes\O-drive\West Chester\Product Data Management\Forecast\IRB Summary\IRB Summary MMYYYY.xlsm", True, "BP24 Returns"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
"EOM Expired Inventory BP49", "\\synthes\O-drive\West Chester\Product Data Management\Forecast\IRB Summary\IRB Summary MMYYYY.xlsm", True, "Expired Inventory BP49"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
"EOM Inv by FCC ALL CODES", "\\synthes\O-drive\West Chester\Product Data Management\Forecast\IRB Summary\IRB Summary MMYYYY.xlsm", True, "Inv by FCC ALL CODES"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
"EOM Inventory All Companies Less Scrwblks", "\\synthes\O-drive\West Chester\Product Data Management\Forecast\IRB Summary\IRB Summary MMYYYY.xlsm", True, "Inventory All Companies"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
"EOM Inventory Corp Purchase by Co", "\\synthes\O-drive\West Chester\Product Data Management\Forecast\IRB Summary\IRB Summary MMYYYY.xlsm", True, "Corp Purchase by Co"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
"NOITEMSINCFFCST", "\\synthes\O-drive\West Chester\Product Data Management\Forecast\IRB Summary\IRB Summary MMYYYY.xlsm", True, "NOITEMSINCFFCST"
End Sub

I cant get this to work for a form though...


Private Sub Command51_Click()

DoCmd.OutputTo acOutputForm, "frm_US_ADD_SMI_BP21", acSpreadsheetTypeExcel10, _
"C:\Documents and Settings\barkerw\Desktop\ACCESS DATABASES\SMI Master List\Exported Files\US_Add\US-Export-Articles-Add.xlsx", True, "US_Exports_Converting"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel10, _
"frm_GITT_Table_US_Add", "C:\Documents and Settings\barkerw\Desktop\ACCESS DATABASES\SMI Master List\Exported Files\US_Add\US_Export_Articles_Add.xlsx", True, "GITT_Table_US_Add"

 

[ranman256]

You cant output a form. (not data)
You CAN transferspreadsheet the form.recordsource. (it is data)