Topics Search

DoCmd.TransferSpreadsheet acExport

DoCmd.TransferSpreadsheet acExport
Views: 175
I have a query with Trace Number in "ascending" order. when I use the code DoCmd.TransferSpreadsheet acExport, the output Excel data are NOT in ascending order by Trace Number. Why is it so
Sponsored Links:

More topics


TransferSpreadsheet with query string

I'm trying to use TransferSpreadsheet with query string, but I keep getting an error.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQueryString, strFileName

is it possible, or do TransferSpreadsheet require either a table or query grid to be used


I have a little piece of code

Sub publish_data()
Const UNC As String = "\\opssvr01\dmi\"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tbl_COMMODITY", UNC & "TEST.xls", True
End Sub

which crashes Access EVERY time it runs

in 2000 or 2003 format

How To Use An SQL Statement In A TransferSpreadsheet Command

I have some some data I would like to output to an excel ss using a SQL statement in my code. Problem is, I don't know how to refer to that sql statement in the transferspreadsheet statement.

Below I have an example. The strSQL variable in the transferspreadsheet statement is obviously incorrect, but just placed to show what I want.

I know I could do it by creating a table and then referring to that created table in the transferspreadsheet function, but would like avoid this if possible.

strSQL = "SELECT table1.SUBID from Table1 where table1.loc = somecriteria"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strSQL, filepathandname, True

checkboxes and VBA not working

I have 4 checkboxes and have differnet queries that go along with it that I want to export to Excel. So the way I written the code is as follows,

So if chkbox1 is clicked then qry1 is run and exported
if chkbox2 is clicked then qry2 is run and exported
if chkbox1 and 2 are clicked then qryall (seprate qry then qry1 and qry2) is run and exported

For Each ctl In MyForm.Controls
Select Case ctl.ControlType
Case acCheckBox
If qry1.Value = True Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry1", fileIn, True, "Export"
ElseIf qry2.Value = True Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry2", fileIn, True, "Export"
ElseIf qry1.value = True and qry2.Value = True Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryall", fileIn, True, "Export"
End If

End Select

however what happens is if I choose both checkboxes then it only reports out the results from checkbox 1. can anyone help to see what I am doing wrong

Opening Excel File After Creation

I am creating an excel file using the following commands (with lots of code before it)

DoCmd.TransferSpreadsheet acExport, , "First Pass Yield Chart Query", "W:\edsplus\applications\0cmad\cmad_chart_spre adsh eet.xls", True, "First_Pass_Yield_Data"

DoCmd.TransferSpreadsheet acExport, , "Average Temp Table", "W:\edsplus\applications\0cmad\cmad_chart_spre adsh eet.xls", True, "First_Pass_Yield_Sum_Average"

And this creates the excel file and puts it in the file location folder and it works fine but it doesn't open the file after it creates it.

DoCmd.transferSpreadsheet cannot delete spreadsheet cell

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "temp_MonthlySalesReport",
"F:\Accounts\Projects\Analysis\Bilings\DSICMM\Ac cess\MonthlySalesReport", True
ModifyExportedExcelFileFormats("F:\Accounts\Projec ts\Analysis\Bilings\DSICMM\Access\MonthlySalesRe port", "temp_MonthlySalesReport1")
End Sub

Is there a way around that well let me iuse the same sheet over again?

Transferspreadsheet: cannot export to .xlsm

and I'm trying to export two tables to a .xlsm macro enabled workbook.

Here's the code:

Function Macro2() On Error GoTo Macro2_Err
DoCmd.TransferSpreadsheet acExport, 10, "Ass+Traf", CurrentProject.Path & "\queries.xlsm", True, "Sheet2"
DoCmd.TransferSpreadsheet acExport, 10, "ParqueSet2010 p/ Gessica", CurrentProject.Path & "\queries.xlsm", True, "Sheet3"
Exit Function Macro2_Err:
MsgBox Error$
Resume Macro2_Exit
End Function

When I replace xlsm for xlsx it works fine, but in the above case I get the following error:

"Cannot update. Database is read-only".

I checked the file properties, and the read-only box is unchecked.

Does anyone know why this happens?

I need to export to a xlsm because I want to build excel macros in the destination file ("queries

TransferSpreadsheet to write to desktop

This command worked for me in access 2003, but doesnt work in 2007 :

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryHistory", "C:\Documents and Settings\All Users\Desktop\History.xls

How should it be changed to write to the desktop in

Export data from queries to worksheetclaibc

Using docmd.transferspreadsheet method which worked perfectly in 2003, mostly worked in 2007 but does not work in 2010! Can you help me with this basic task.

Used the following:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Queryname", "wr\bhgc\Data\analysis"

Transferring 6 queries to worksheets within one file (xlsb)
This should then be picked up in another spreadsheet when update links but this also does not work. Also worked perfectly in office 2003 but not in 2007 or

Unable to copy Excel worksheets in VBA

Here is the code I am using in Access 2007 to manipulate Excel.
The sheets are renamed, but the workbooks remain hidden, and will not copy. It fails on the Copy After command.
I am using Excel 2007.
Any idea what is wrong?
DoCmd.TransferSpreadsheet acExport, 10, "tbl_A, "C:\A.xlsx, True
DoCmd.TransferSpreadsheet acExport, 10, "tbl_B", "C:\B.xlsx, True

Set MyXl = GetObject("C:\A.xlsx")
Set MyXl1 = GetObject("C:\B.xlsx")

MyXl.Worksheets(1).Name = "A_Sheet"
MyXl1.Worksheets(1).Name = "B_Sheet"

MyXl1.Worksheets("B_Sheet").Copy After:=MyXl.Worksheets("A_Sheet