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  2463  nico5038 3,080
Recognized Expert Specialist
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?
nico5038 3,080
Recognized Expert Specialist
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", "MicrosoftExcel Biff8(*.xls)", "L:\~Public\Dat aBase\Sustainin g Engineering\Sus taining 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("E xcel.Applicatio n")
Set xlswkb = GetObject("L:\~ Public\DataBase \Sustaining Engineering\Do not Rmove - Jim A 10-1-07\issues.xls")
xlswkb.Applicat ion.Run "issues.xls!for matting"
End Sub
Excell:
Sub formatting()
Cells.Select
Selection.Rows. AutoFit
Selection.Colum ns.AutoFit
Range("B3").Sel ect
ActiveWindow.Fr eezePanes = True
Range("A1").Sel ect
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.
nico5038 3,080
Recognized Expert Specialist
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, I know how
to do this. I'm preferring to keep the Excel instance invisible, but
while the macro is running, it would be nice to see some sort of
progress indicator.
(By the way, here's basically what the Excel macro is doing: it's
opening...
|
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 converted
Access macro can do the same thing as the Excel macro does when i open
the Excel file.
Can anybody give me a hint? Thanks in advance!
Alex
|
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,
Contents:=True, Scenarios:=True
When I call this macro from the database all works fine except when I
recorded the macro I deselected the "Select locked cells" option under the
"Allow all users of this worksheet to:" section but yet when I access the...
|
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.
My code is:
Sub Run_Excel_Macro()
Dim xls, xlWB As Object
| | |
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
DoCmd.OpenQuery "qry_delete FCL"
DoCmd.OpenQuery "qry_temp FCL"
|
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 macro. (I'll get to using VB later on).
What I would like to do is import a single workbook w/three seperate
worksheets into three seperate access tables AND truncate the time stamp
that is used in the excell sheet via a macro.
|
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 run everytime I open excel.
When I navigate to the excel file, and open it, it autoruns the macro with no problems. When I go from Access hit the command, the excel table opens with no problem, but the macro does not auto run on start.
Here is...
|
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 generated Excel spreadsheet from an Access Module. I can create the spreadsheet, button, and I have the macro to insert into command button. But I am not sure how to assign the macro to the button in the access module.
Set xlApp =...
|
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: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
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: 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: 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 into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
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
| |
