469,939 Members | 2,345 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,939 developers. It's quick & easy.

access 2003 export to Excel 2003 worksheets

Hello,

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"
Excel_Opmaak

appExcel.Sheets("Mutation_AST_Workstation").Select
Excel_Opmaak

appExcel.Sheets("New_AST_Laptop").Select
Excel_Opmaak

appExcel.Sheets("New_AST_Workstation").Select
Excel_Opmaak

appExcel.Sheets("Mutation_AST_Laptop").Select

Mcr_Export_ScanControlDetailTypetoExcel_Exit:
Exit Function

Mcr_Export_ScanControlDetailTypetoExcel_Err:
MsgBox Error$
Resume Mcr_Export_ScanControlDetailTypetoExcel_Exit

End Function

------------------
Has anyone a suggestion?

With kind regards,

Sander van Ee
Jul 5 '07 #1
0 2376

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Anthony Cuttitta Jr. | last post: by
6 posts views Thread by Elena | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.