Question
Thread display: Collapse - |
All Answers
Share your knowledge
Start or search
Create a new discussion
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
Access 2003 export to Excel sheets 2003
I am trying to export several Access 2003 select queries to Excel 2003 using an Access macro. I'm using the macro command "TransferSpreadsheet" to export the queries. This going quit well, but I've one problem.
The first problem is that in Excel apostrophes ('), carets (^), quotation marks ("), or backslashes (\) appear in the formula bar but not in the cells, like it does when you import in Excel from Lotus. When I use the command "CmD.Output" to Excel this doesn't appear. A little problem, but quit annoying because the characters are taken over by a copy and paste action.
The code I use is:
--------------------
Function Mcr_Export_ScanControlDetailTypetoExcel()
On Error GoTo Mcr_Export_ScanControlDetailTypetoExcel_Err
Dim strdate As String
strdate = Format$(Date, "yyyymmdd")
Set filesys = CreateObject("Scripting.FileSystemObject")
If filesys.FileExists("D:\My Documents\Desktop\Scancontrol_DetailType-" & strdate & ".xls") Then
filesys.DeleteFile "D:\My Documents\Desktop\Scancontrol_DetailType-" & strdate & ".xls"
End If
Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = True
appExcel.Application.WindowState = xlMaximized
appExcel.ScreenUpdating = True
' Exporteren 'Report Customer Information' naar Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Mutation_AST_Laptop", "D:\My Documents\Desktop\Scancontrol_DetailType-" & strdate & ".xls", True, ""
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Mutation_AST_Workstation", "D:\My Documents\Desktop\Scancontrol_DetailType-" & strdate & ".xls", True, ""
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "New_AST_Laptop", "D:\My Documents\Desktop\Scancontrol_DetailType-" & strdate & ".xls", True, ""
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "New_AST_Workstation", "D:\My Documents\Desktop\Scancontrol_DetailType-" & strdate & ".xls", True, ""
appExcel.Workbooks.Open "D:\My Documents\Desktop\Scancontrol_DetailType-" & strdate & ".xls"
------------------
Has anyone a suggestion?
With kind regards,
Sander van Ee