473,554 Members | 2,315 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Format Excel worksheet from inside Access 2003 application

Hi everyone,

Looking for some help or ideas, on the folloiwng problem.

Have an Access 2003 application that creates an Excel file containing
data from an Access parameter query. This is accomplished with the
following line:

DoCmd.TransferS preadsheet acExport, , "MyQuery", "ExcelFileName" , True

That's the easy part. The problem is, the audience this Excel file is
intended for requires the workbook created by the above line to not
only contain the proper data (provided by the query), but also, for
the worksheet containing that data to be formatted in a specific way.

I would like to be able to write some VBA code to format the worksheet
to the required specs. The actual code that does the formatting can be
easily created by recording the steps of a macro in Excel while I
format the sheet as desired. Now, I would like to be able to run that
code from inside Access, right after the above line
(DoCmd.Transfer Spreadsheet acExport....) runs.

1) Ideally, I would like Access itself, via the VBA code, to
manipulate (format) the Excel sheet just created, so this could happen
in any computer, whether Excel is installed in it or not. Is this
possible? How?

2) If that is not possible, and Excel NEEDS to be launched from the
Access VBA code to achieve this, can it be done in a completely silent
mode, so the Access application user is not aware that Excel launched,
formatted a sheet and closed? If that is the only manner to achieve my
goal, how can this be accomplished?
Nov 11 '08 #1
1 3723
"Joe Humburg" <Jo*@humburg.ne twrote in message
news:tg******** *************** *********@4ax.c om...
Can't thank you enough for your extremely fast and detailed response,
Keith. Will give it a try right away.

Thanks !!!!!!!
No problem, hope it helps.

Keith.
www.keithwilby.co.uk

Nov 12 '08 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
4033
by: Mr. Smith | last post by:
Hello all, My code can successfully open, write to, format and save several worksheets in a workbook then save it by a given name, close and quit excel. My problem is that if I try and do it again, Excel hangs. OR if I open Excel again (say from a desktop icon) before I close Access, Excel hangs. (this has happened for both 97 & 2000...
4
8832
by: BerkshireGuy | last post by:
I have the following Access code that exports to Excel, inserts a title, changes the color of the title, and then changes the format of several columns to currency. The following code sometimes works and sometimes doesnt. When it doesnt, it gives me a 1004 "Method of Columns" of object variable failed error. My only guess is a timing...
9
2810
by: Anthony | last post by:
To me, creating Excel 2003 spreadsheets programmatically via VB.NET hasn't really changed since the days of VB6. That is, I'd do something similar to this Code: Dim ExcelApp As Excel.Application Dim ExcelWB As Excel.Workbook
3
10713
by: James Wong | last post by:
Dear all, I have an old VB6 application which can create and access Excel object. The basic definition statements are as follows: Dim appExcel As Object Dim wkb1 As Excel.Workbook Dim wks1 As Excel.Worksheet Set appExcel = New
8
788
by: Sam | last post by:
Hi All, I posted this message few days ago. No one has answered so far. I just would like to know if it is possible to do or not. Even if you can not tell me how to do this, maybe you know which library I need to use in order to access excel information from a VB.NET code. For example, I would be interested by knowing how to access a simple...
3
3991
by: dan_roman | last post by:
Hi, I developed a script with a nice interface in Tkinter that allows me to edit some formulas and to generate an Excel worksheet with VBA macros within it. The script runs perfectlly in Office 2000, but in Office 2003 crash at line: "wbc = workbook.VBProject.VBComponents.Add(1)" Please help me :-( the code of the module that crash is...
2
2874
by: sherifffruitfly | last post by:
Hi, I'm using an adaptation of excel-reading code that's all over the internet - I don't much like or understand it, but it has worked for me in the past.... beggars can't be choosers... : Excel.Application excelObj = new Excel.Application(); Excel.Workbook theWorkbook = excelObj.Workbooks.Open(path+filename, 0, true, 5, "", "", true,...
9
4524
by: Doug Glancy | last post by:
I got the following code from Francesco Balena's site, for disposing of Com objects: Sub SetNothing(Of T)(ByRef obj As T) ' Dispose of the object if possible If obj IsNot Nothing AndAlso TypeOf obj Is IDisposable Then DirectCast(obj, IDisposable).Dispose()
6
9006
by: and1 | last post by:
hello .. hmm currently i'm working on vba in excel, apparently i use ADO to extract a table data from access to excel and it works fine. the problem is when i use the extracted data to create a chart using vba and it doesn't seem to display the data properly.. it is due to the text format when the data is extracted to excel.. how can i change it...
0
8053
jinu1996
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...
1
7576
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...
0
6163
agi2029
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...
0
5165
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3583
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3564
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2027
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
1
1147
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
853
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.