Hi all,
I am using Access 2003 as well as Excel 2003.
I have an Access database that has an output to Excel button I created. This outputs a form to an Excel file A. I then have Excel file B formatted and linked to A. I would like to do this without the link, where when I export to Excel, it automatically formats the Fields. If I run the macro in Excel to format on open, every time I export the file it deletes the macro. So this would be something I need to run in Access. The file is on the network for everyone to use, so I need a way to do this without going to everyones computer to make it work.
Thanks for your help in advance.
Brian Smith
8 2442
My approach would be to create a query with the proper formatting for Excel.
You can use the Format() function for most cases.
What's the specific need in formatting the fields ?
Nic;o)
I am wanting to have all the information auto fit, freeze panes, and save with a filename with date and time. I want to be able to do this time after time, and be able to open Excel without running the macro. Right now, I am using an Excel file, with the macros I need for formatting, to do this. I would like to get rid of that file to save space for Excel file being created. How do I do this in a query?
Sorry, but freezing panes, etc. can't be done using a query.
This will require "automation" and some VBA coding against the Excel object model....
How well are your coding skills ?
Nic;o)
Not very good, I am learning everything I have done off the net, and doing some manipulating to make it work for me. I can show you what I have so far in Access and in Excel.
Access:
Private Sub excel_Click()
On Error GoTo Macro1_Err
DoCmd.OutputTo acForm, "Issues lookup", "MicrosoftExcelBiff8(*.xls)", "L:\~Public\DataBase\Sustaining Engineering\Sustaining DB Export " & Format(Now(), "mm-dd-yy hh_mm_ss AMPM") & ".xls", True, "", 0
GoTo Skip_Err
Macro1_Err:
MsgBox Error$
Skip_Err:
Dim xlswkb As Object
Set xlswkb = CreateObject("Excel.Application")
Set xlswkb = GetObject("L:\~Public\DataBase\Sustaining Engineering\Do not Rmove - Jim A 10-1-07\issues.xls")
xlswkb.Application.Run "issues.xls!formatting"
End Sub
Excell:
Sub formatting()
Cells.Select
Selection.Rows.AutoFit
Selection.Columns.AutoFit
Range("B3").Select
ActiveWindow.FreezePanes = True
Range("A1").Select
ActiveWorkbook.Save
End Sub
This is how I get it to do what I want. Now the Access just goes to this Excel form to just run the macros, and thats all. It creaqtes a diffrent Excel form to do the macros in.
The link is not working, it says page not found.
Sorry, forgot the ".html", try again :-)
Nic;o)
Modified the VBA to try to work with my Databse, and get a compile error on "Dim as..." parts. Any ideas?
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mrs Howl |
last post by:
I don't know if there's even a way to do what I want. I click on a
button in an Access form, and it opens an instance of Excel, and opens
a workbook and runs a macro that's in it. So far, fine,...
|
by: Alex |
last post by:
i have a module in Access which opens an existing Excel file and
envokes a macro within the Excel file to draw graphs. now i am trying
to convert the Excel macro to an Access one so that the...
|
by: Giganews |
last post by:
I have an Access 97 database in which I am running an Excel macro through
automation. The macro in Excel is as follows:
Worksheets("Sheet1").Protect Password:="****", DrawingObjects:=True,...
|
by: geronimo_me |
last post by:
Hi,
I have the following code in an access module:
Sub Run_Excel_Macro()
Dim xls, xlWB As Object
Dim strFile, strMacro As String
|
by: geronimo_me |
last post by:
Hi,
I am trying to run an Excel macro from an Access module, however when I
run the code the macro runs but then I get an error in Access. The
error is: Run-time error "440", Automation error.
...
|
by: bwhite |
last post by:
I have a temp table with one row of data that I need to export into
Excel. I created the export to create the xls file as follows ...
Dim FileName
FileName = !!
DoCmd.SetWarnings False...
|
by: Nick M |
last post by:
Hello All,
Excellent info here Thanks!
I am very new to using access in general and I am on a learning curve.
I'm trying to import an excel workbook (with worksheets) into an access db
via a...
|
by: mld01s |
last post by:
Hi all!!
I need help, I have been stuck for a few days on this one. I am trying to open an excel table from a command button in Access. The excel table has an auto_open macro, that is supposed to...
|
by: MitchR |
last post by:
Good Morning Folks;
I have a question that is pretty far fetched but here goes nothing... I am looking to find a way to insert a macro into an Excel command button located in an Access VBA...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |