473,699 Members | 2,278 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

access 2003 export to Excel 2003 worksheets

1 New Member

I am trying to export several Access 2003 select queries to Excel 2003 using an Access macro. I'm using the macro command "TransferSpread sheet" 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_Scan ControlDetailTy petoExcel()
On Error GoTo Mcr_Export_Scan ControlDetailTy petoExcel_Err

Dim strdate As String

strdate = Format$(Date, "yyyymmdd")

Set filesys = CreateObject("S cripting.FileSy stemObject")
If filesys.FileExi sts("D:\My Documents\Deskt op\Scancontrol_ DetailType-" & strdate & ".xls") Then
filesys.DeleteF ile "D:\My Documents\Deskt op\Scancontrol_ DetailType-" & strdate & ".xls"
End If

Set appExcel = CreateObject("E xcel.Applicatio n")

appExcel.Visibl e = True
appExcel.Applic ation.WindowSta te = xlMaximized
appExcel.Screen Updating = True

' Exporteren 'Report Customer Information' naar Excel
DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel9, "Mutation_AST_L aptop", "D:\My Documents\Deskt op\Scancontrol_ DetailType-" & strdate & ".xls", True, ""
DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel9, "Mutation_AST_W orkstation", "D:\My Documents\Deskt op\Scancontrol_ DetailType-" & strdate & ".xls", True, ""
DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel9, "New_AST_Laptop ", "D:\My Documents\Deskt op\Scancontrol_ DetailType-" & strdate & ".xls", True, ""
DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel9, "New_AST_Workst ation", "D:\My Documents\Deskt op\Scancontrol_ DetailType-" & strdate & ".xls", True, ""

appExcel.Workbo oks.Open "D:\My Documents\Deskt op\Scancontrol_ DetailType-" & strdate & ".xls"

appExcel.Sheets ("Mutation_AST_ Workstation").S elect

appExcel.Sheets ("New_AST_Lapto p").Select

appExcel.Sheets ("New_AST_Works tation").Select

appExcel.Sheets ("Mutation_AST_ Laptop").Select

Mcr_Export_Scan ControlDetailTy petoExcel_Exit:
Exit Function

Mcr_Export_Scan ControlDetailTy petoExcel_Err:
MsgBox Error$
Resume Mcr_Export_Scan ControlDetailTy petoExcel_Exit

End Function

Has anyone a suggestion?

With kind regards,

Sander van Ee
Jul 5 '07 #1
0 2667

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

Similar topics

by: sridevi | last post by:
Hello How to export data from ms-access database to excel worksheet using ASP. mainly i need to export data to multiple worksheets. it is very urgent to us. i have a sample code which works only exporting to single worksheet. but i need to export data to multiple worksheets. it is very urgent to us. so please help me in code.
by: DFS | last post by:
" has detected corruption in this file. To try to repair the corruption, first make a backup copy of the file. Then, on the Tools menu, point to Database Utilities and click Compact and Repair database. If you are currently trying to repair this corruption then you will need to recreate this file or restore it from a previous backup." I think it's my usage of the Excel 9.0 object library that's causing the problem, but I can't be...
by: Anthony Cuttitta Jr. | last post by:
I'm working on some procedures where Access queries are exported to Excel, and then later on, those same workbooks are openned, and I need to target a specific original sheet. Sometimes there will be multiple sheets in the same workbook, sometimes only a single tab. The issue is this: Save query "XLTest - qryExport" to file and the worksheet name becomes "XLTest___qryExport" (three underscores). I have a generic procedure which...
by: cybertof | last post by:
Hello, Is there a way to connect (through automation) a c# application to a running Excel 2003 instance on a specific workbook ? In the past, i used to use GetObject(...) function in VB6. Regarding C#, I have heard about System.Runtime.InteropServices.marshal.GetActiveObject ("Excel.Application")
by: Elena | last post by:
I'm trying to export data to an Excel worksheet. I can export the data in the cell values perfectly. I need the code to change a header and footer for the worksheet, not for the columns. Is this possible? If so, I really need the code immediately. Thank you, Elena
by: rlntemp-gng | last post by:
RE: Access 2003/Excel 2003 Problem: After I close the Access application completely, I go out to the Task Manager and there is an Excel.exe object still sitting out there. My Access application creates an Excel file (MyTestFile.XLS) just fine per the code submitted here. This code closes the Excel file created and close the Excel application, leaving the Access app open. Then I close Access. After Access is closed, I go to the task...
by: semijoyful | last post by:
OS: Win XP SP2 Access version: 2003 Excel version: 2003 I am new at this, as I am sure you have gathered from this post title:) I am working on a form where users can input data in Access and at the end of the day be able to press a button on the form that would update that data to a single exel spreadsheet. The following is the code for the module. When I click on the button in the form, It takes me to VBS Debugger. I then type...
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am trying to export to Excel using a command in an Access Form. RowID strFY AccountID CostElementWBS 1 2008 1 7 2 2008 1 7 I want to...
by: DennisBetten | last post by:
First of all, I need to give some credit to Mahesh Chand for providing me with an excellent basis to export data to excel. What does this code do: As the title says, this code is capable of extracting all tables and it's data from any given database! I was searching the net for a program like this, but I didn't come accross any (free) versions. So I decided to write it myself. To get this code to work, you need to add a reference to...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.