DoCmd.TransferSpreadsheet acExport

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:

Related 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

Excel Export Tab Name
I have the below "TransferSpreadsheet" command running. However, whenever it does run it names that tab "_CR2" instead of "CR2". Any idea why? I'm plum out. Thanks in advance!

DoCmd.TransferSpreadsheet acExport, , strTableName, strFileLoc & strFileName & strFileExt, True, "CR2"

TransferSpreadsheet with SQL String
In the place of Table Name below, I have put the name of one of my sql table.

DoCmd.TransferSpreadsheet acExport, , "qF01", "D:\Stock.xls", True, "Form01"

I want to know whether I could put the sql string like Select * from qF

DoCmd.TransferSpreadsheet not consistently working
I'm using the code below to transfer a query to a specific Excel file. It works, and when I click the command button again, it overwrites the existing Excel sheet. However, after several times of overwriting the Excel sheet, it stops working or it does anymore overwrite.

Is there an expiration for the code? I mean, does it work for only a maximum number of times? Say it can overwrite only up to 5 times. After which, the code won't work anymore.

DoCmd.TransferSpreadsheet acExport, 10, "queryname", "destinationExcelFile", False, ""

DocName alias and TransferSpreadsheet
I am using the TransferSpreadsheet method to export a query to a new excel workbook/worksheet. My users need the tab of the new worksheet to be called "Sheet1" due to some vlookups they have in another workbook. I have tried to use the "Range" property but it doesn't seem to work. It is naming the tab the same as my query. Is there a way to give it an alias of "Sheet1" somewhere in my VBA code?
I don't want them to have to open the spreadsheet and rename it each time if possible.

Private Sub cmdSP11_Click()
On Error GoTo Err_cmdSP11_Click
Dim stDocName As String

stDocName = "qSource_Data_SP11" --alias this to "Sheet1" ?

'DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, stDocName, filepath\\SP11\SP11___Download.xls"

Exit Sub
MsgBox Err.Description
Resume Exit_cmdSP11_Click

TransferSpreadsheet with SQL String
Dear folks
In the place of Table Name below, I have put the name of one of my sql table.
DoCmd.TransferSpreadsheet acExport, , "qF01", "D:\Stock.xls", True, "Form01"
I want to know whether I could put the sql string like Select * from qF01.

Read-only Excel file after using TransferSpreadsheet Method
After exporting a query to an existent [Compatibility mode] Excel file, using DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,.and opening the Excel file with

.Workbooks.Open(., , true) I'm getting a [read-only] [Compatibility mode] Excel file. Is there a way to get a only [Compatibility mode] file?

TransferSpreadsheet - Use first row as field names
when using the TransferSpreadsheet method how do you tell Access to use the first row as headers for the table? Here is my current code.

DoCmd.TransferSpreadsheet acImport, , "tblOvertime_Import", vFilePath & vFileName

Frustrated With Transfer Error . .
I can't understand why this is not working.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryMyResults", "K:\SouthWest\Analysis DB\Weekly test\Book1.xlsx", True

The code saves the file, but when I try to open it, I get an error message for an invalid format or file extension

docmd.transferspreadsheet crashes access
I am having some trouble with the docmd.transferspreadsheet function, everytime I run it, it crashes Access. I have used the docmd.outputto function and that works fine but I need the queries to be in the same excel file but in different sheet and the docmd.outputto open a new excel file for each query.


I have tried to display an error message but Access just freezes and I get the program not responding message |

TransferSpreadsheet method ERROR: Access engine could not find the object 'CurrentDeptList'
'I'm using the TransferSpreadsheet method to export a table called 'CurrentDeptList' from Access 2007 into Excel 2003. IT WAS WORKING FINE YESTERDAY!

for no apparent reason, every time I run it I get the following error: "Run-time error '3011' The Microsoft Office Access engine could not find the object 'CurrentDeptList'. Make sure the object exists . . ."

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CurrentDeptList", ExcelDirPath & "\" & "CusNameDeptChng.xls"

Here's what I've tried:

1) I ran it from 5 different backup copies - Same result!

2) I've completely re-installed Access 2007 and MS Office 2003.
Not only won't this work, but now I can't get any DoCmd.Transfer methods working.

3) I added 5 more Microsoft References in. I also copied my database to another PC with Access 2007 and got the - Same result!

4) I opened a blank database and imported ALL the objects from my DB into it - Same result!