How to resolve Run-time error '3010' ?
Do you know why the compiler stops on the line below and gives me the following message in the following procedure:
I am trying to run a query and export it to Excel.
--------------------------
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryFA", "c:\FA.xls"
Run-time error '3010':
Table 'qryFA' already exists.
--------------------------
When you export a query to excel it takes the name of the query as name of the sheet
you can rename your query with NON-CTS and export it. or use the code below to transfer and rename the sheet
--------------------------
Private Sub cmdTransfer_Click()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryFA", "c:\FA.xls"
Set xlApp = CreateObject("Excel.Applic
' Open the spreadsheet to which you exported the data.
Set xlBook = xlApp.Workbooks.Open("C:\F
Set xlSheet = xlBook.Worksheets("qryFA")
'rename the sheet
xlSheet.Name = "NON-CTS"
xlBook.Saved = True
Set xlSheet = Nothing
Set xlBook = Nothing
'xlApp.Quit
Set xlApp = Nothing
End Sub
I am trying to run a query and export it to Excel.
--------------------------
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryFA", "c:\FA.xls"
Run-time error '3010':
Table 'qryFA' already exists.
--------------------------
When you export a query to excel it takes the name of the query as name of the sheet
you can rename your query with NON-CTS and export it. or use the code below to transfer and rename the sheet
--------------------------
Private Sub cmdTransfer_Click()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryFA", "c:\FA.xls"
Set xlApp = CreateObject("Excel.Applic
' Open the spreadsheet to which you exported the data.
Set xlBook = xlApp.Workbooks.Open("C:\F
Set xlSheet = xlBook.Worksheets("qryFA")
'rename the sheet
xlSheet.Name = "NON-CTS"
xlBook.Saved = True
Set xlSheet = Nothing
Set xlBook = Nothing
'xlApp.Quit
Set xlApp = Nothing
End Sub
Ah - I see you got it from Cap.
So it should work - I wonder why it isn't.
Confirm what I've asked above.
Should you perhaps have
xlBook.Save
instead of
xlBook.Saved = True
(I'd have thought that would discard changes rather than persist them - as it thinks the book is then saved).
And that would then leave you a qryFA sheet in your book as is being reported
So it should work - I wonder why it isn't.
Confirm what I've asked above.
Should you perhaps have
xlBook.Save
instead of
xlBook.Saved = True
(I'd have thought that would discard changes rather than persist them - as it thinks the book is then saved).
And that would then leave you a qryFA sheet in your book as is being reported
IF You have file ,you need to delete it ,use KILL command
Kill "c:\FA.xls"
DoCmd.TransferSpreadsheet acExport,acSpreadsheetType
I am prefer to use
docmd.OutputTo
Kill "c:\FA.xls"
DoCmd.TransferSpreadsheet acExport,acSpreadsheetType
I am prefer to use
docmd.OutputTo
I think the intention is to caress the sheet out of the way rather than blat it out of existence lol
Although that raises a point.
If you rename it to "NON-CTS" then you'll not be able to remane the next one to that.
Perhaps you might consider renaming it to something based on the date/time?
Then you could have a progression of sheets - from particular exports.
Although that raises a point.
If you rename it to "NON-CTS" then you'll not be able to remane the next one to that.
Perhaps you might consider renaming it to something based on the date/time?
Then you could have a progression of sheets - from particular exports.
ASKER
Kill "c:\FA.xls"
causes
Runtime error '70'
Permission denied
causes
Runtime error '70'
Permission denied
You'd have to do that before you open the file of course?
And make sure that no phantom Excel apps might still have a hold on it.
Is that actually what you want to do then?
And make sure that no phantom Excel apps might still have a hold on it.
Is that actually what you want to do then?
ASKER
How about this:
Is there a way to run the query qryFA and SELECT into Table tblTest without displaying the result set of the query qryFA.
Then DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblTest", "c:\FA.xls", NOCTR
Is there a way to run the query qryFA and SELECT into Table tblTest without displaying the result set of the query qryFA.
Then DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblTest", "c:\FA.xls", NOCTR
Have you tried what we've asked though?
Have you opened c:\FA.xls and made sure there isn't already a sheet named qryFA ?
If so - get rid of it.
Then make the changes regarding saving it before running the code again.
See if it works - if it does, run it again - see if it works again.
As for selecting all into tblTest, well yes, you could.
But what would that achieve?
You'll still have to prevent there from being more than one sheet named tblTest!
Have you opened c:\FA.xls and made sure there isn't already a sheet named qryFA ?
If so - get rid of it.
Then make the changes regarding saving it before running the code again.
See if it works - if it does, run it again - see if it works again.
As for selecting all into tblTest, well yes, you could.
But what would that achieve?
You'll still have to prevent there from being more than one sheet named tblTest!
Create "c:\FA.xls" manualy.Open it rename sheet to NON-CTS.
Go to your Access application.File ->get External data->Link tables _> change file tape to xls and link your spreadsheet.
You can Use Excel Link table.
Private Sub cmdTransfer_Click()
docmd.runsql "delete * from [NON-CTS]"
docmd.runsql "INSERT INTO [NON-CTS] SELECT qryFA.* FROM qryFA;"
end sub
Go to your Access application.File ->get External data->Link tables _> change file tape to xls and link your spreadsheet.
You can Use Excel Link table.
Private Sub cmdTransfer_Click()
docmd.runsql "delete * from [NON-CTS]"
docmd.runsql "INSERT INTO [NON-CTS] SELECT qryFA.* FROM qryFA;"
end sub
ASKER
How do I display the Spreadsheet automatially on the monitor after the user clicks a button to trigger the procedure ?
ASKER
How do I display the Spreadsheet automatially on the monitor after the user clicks a button to trigger the following procedure ?
Private Sub List150_DblClick(Cancel As Integer)
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
If (Len(Dir("C:\FA.XLS")) > 0) Then
Kill "C:\FA.XLS"
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryFA", "C:\FA.XLS"
Set xlApp = CreateObject("Excel.Applic
' Open the spreadsheet to which you exported the data.
Set xlBook = xlApp.Workbooks.Open("C:\F
Set xlSheet = xlBook.Worksheets("qryFA")
' rename the sheet
xlSheet.Name = "NON-CTS"
xlBook.Saved = True
Set xlSheet = Nothing
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
'myCostCenter = List150.List(0)
'Run qryFa
'DoCmd.OutputTo acOutputQuery, "qryFA", acFormatXLS, "c:\FA.xls", True, "NON-CTS"
End Sub
Private Sub List150_DblClick(Cancel As Integer)
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
If (Len(Dir("C:\FA.XLS")) > 0) Then
Kill "C:\FA.XLS"
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryFA", "C:\FA.XLS"
Set xlApp = CreateObject("Excel.Applic
' Open the spreadsheet to which you exported the data.
Set xlBook = xlApp.Workbooks.Open("C:\F
Set xlSheet = xlBook.Worksheets("qryFA")
' rename the sheet
xlSheet.Name = "NON-CTS"
xlBook.Saved = True
Set xlSheet = Nothing
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
'myCostCenter = List150.List(0)
'Run qryFa
'DoCmd.OutputTo acOutputQuery, "qryFA", acFormatXLS, "c:\FA.xls", True, "NON-CTS"
End Sub
You already have that code
In your original procedure immediately before and after the suggestful line
' Open the spreadsheet to which you exported the data.
Just don't then execute the name change
'rename the sheet
'xlSheet.Name = "NON-CTS"
In your original procedure immediately before and after the suggestful line
' Open the spreadsheet to which you exported the data.
Just don't then execute the name change
'rename the sheet
'xlSheet.Name = "NON-CTS"
What are you doing?
Everything at the bottom is commented out.
What exactly have you done regarding solving the problem you asked about?
Have you even tried any of the suggestions?
Everything at the bottom is commented out.
What exactly have you done regarding solving the problem you asked about?
Have you even tried any of the suggestions?
<sighs>
Do you mean make Excel the active application?
At a really simple level...
AppActivate "Microsoft Excel - FA"
Do you mean make Excel the active application?
At a really simple level...
AppActivate "Microsoft Excel - FA"
ASKER
Yes I meant to make Excel the active application and display the spreadsheet when the user double clicks on a control.
AppActivate "Microsoft Excel - FA"
causes a Runtime error '5'
Invalid Procedure call or argument
Where do I insert this statement anyway ?
--------------------------
Private Sub List150_DblClick(Cancel As Integer)
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
If (Len(Dir("C:\FA.XLS")) > 0) Then
Kill "C:\FA.XLS"
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryFA", "C:\FA.XLS"
Set xlApp = CreateObject("Excel.Applic
' Open the spreadsheet to which you exported the data.
Set xlBook = xlApp.Workbooks.Open("C:\F
Set xlSheet = xlBook.Worksheets("qryFA")
' rename the sheet
' xlSheet.Name = "NON-CTS"
xlBook.Saved = True
Set xlSheet = Nothing
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
AppActivate "Microsoft Excel - FA"
causes a Runtime error '5'
Invalid Procedure call or argument
Where do I insert this statement anyway ?
--------------------------
Private Sub List150_DblClick(Cancel As Integer)
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
If (Len(Dir("C:\FA.XLS")) > 0) Then
Kill "C:\FA.XLS"
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryFA", "C:\FA.XLS"
Set xlApp = CreateObject("Excel.Applic
' Open the spreadsheet to which you exported the data.
Set xlBook = xlApp.Workbooks.Open("C:\F
Set xlSheet = xlBook.Worksheets("qryFA")
' rename the sheet
' xlSheet.Name = "NON-CTS"
xlBook.Saved = True
Set xlSheet = Nothing
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ignor my comments about Linkin to Excel microsoft not support deete from Link Excel table.May be your have this trable because you don't have permission foer this file(or folder).ormay be you stoped code and not closed xlSheet object.In this case reboot your computer try this again
Kill "c:\FA.xls"
DoCmd.TransferSpreadsheet acExport,acSpreadsheetType
Good luck
Kill "c:\FA.xls"
DoCmd.TransferSpreadsheet acExport,acSpreadsheetType
Good luck
Try
--------------------------
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryFA", "c:\FA.xls", "NON-CTS_" & Fromat(Now(),"YYYY_MM_DD_H
--------------------------
--------------------------
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryFA", "c:\FA.xls", "NON-CTS_" & Fromat(Now(),"YYYY_MM_DD_H
--------------------------
Did you get that code from somewhere - or write it yourself?