Thank you for taking the time to report an issue.
What's wrong... Please write below.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQueryString, strFileName
is it possible, or do TransferSpreadsheet require either a table or query grid to be used
Const UNC As String = "\\opssvr01\dmi\"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tbl_COMMODITY", UNC & "TEST.xls", True
which crashes Access EVERY time it runs
in 2000 or 2003 format
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
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
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"
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
"F:\Accounts\Projects\Analysis\Bilings\DSICMM\Ac cess\MonthlySalesReport", True
ModifyExportedExcelFileFormats("F:\Accounts\Projec ts\Analysis\Bilings\DSICMM\Access\MonthlySalesRe port", "temp_MonthlySalesReport1")
Is there a way around that well let me iuse the same sheet over again?
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.
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:
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
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
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
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"