Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-27-2019, 04:32 AM   #1
peskywinnets
Newly Registered User
 
Join Date: Feb 2014
Location: London, UK
Posts: 517
Thanks: 278
Thanked 5 Times in 5 Posts
peskywinnets is on a distinguished road
Exporting as Excel file from Access (wanting to run an Excel macro)

So, I' pretty up to speed with Access VBA, but haven't really done a lot with Excel.

What I'm trying to achieve is export a file from Access as an Excel file (this bit I know how to do), then be able to run an Excel Macro on that Excel file.

Access only allows me to export as xls....but wouldn't I need the Excel file to have an xlsm extension before I can run an Excel macro on that Excel file? (If so, I'll need to get MS access to do a rename after the export)

I want to be able to use some pre-coded Excel VBA on these newly created excel files (i.e. the files exported from Access) ...presumably I['m going to have to store my Excel VBA code in a Personal.xlsb ? (Excel seems a bit of a convoluted mess wrt storing & calling VBA actually!)

peskywinnets is offline   Reply With Quote
Old 01-27-2019, 04:57 AM   #2
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,850
Thanks: 411
Thanked 691 Times in 670 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Exporting as Excel file from Access (wanting to run an Excel macro)

When you run a macro, it does not need to be in a particular file. Each user has a personal.xlsb that can store macroes as you know, but any other file open would work.? I have ones that I would use against various excel workbooks stored there.

Plus if you export to the old xls format, you can store macroes there without Excel complaining.

Have you thought about pulling the data from Access to Excel from Excel.?

That way you can record the macro steps and then tune to your requirements.?

HTH
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 01-27-2019, 05:00 AM   #3
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,168
Thanks: 495
Thanked 904 Times in 856 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Exporting as Excel file from Access (wanting to run an Excel macro)

On a "VBA" tangent from your question, I've just completed a new "Drop-in Component" for Microsoft Access called "Show/Hide and Export to Excel". You can see details about it on my website here:-

http://www.niftyaccess.com/show-hide-export-to-excel/

It incorporates this excellent code:-

https://btabdevelopment.com/export-a...uery-to-excel/

which I believe is originally from Bob Larson.

Now I know you specified a Macro, however I'm thinking you might be better off modifying Bobs VBA code.

The drop-in component is normally 6.99, however you can get it for free if you sign up to my newsletter. Details are on the Nifty Access web page linked to above.

__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 01-27-2019, 05:28 AM   #4
peskywinnets
Newly Registered User
 
Join Date: Feb 2014
Location: London, UK
Posts: 517
Thanks: 278
Thanked 5 Times in 5 Posts
peskywinnets is on a distinguished road
Re: Exporting as Excel file from Access (wanting to run an Excel macro)

Heres what I'm doing in Access (VBA)...

DoCmd.RunSavedImportExport "Export-Paypal_Report"

this exports to an excel file (with formatting) & also opens up excel straight away.

I then want to be able to run some VBA on that Excel worksheet (that was opened via access) all this VBA does is format the data in Excel (a one off run at the beginning)

Quote:
Originally Posted by Gasman View Post
Have you thought about pulling the data from Access to Excel from Excel.?

That way you can record the macro steps and then tune to your requirements.?

HTH
This is interesting, but my Excel chops are weak (whereas my Access VBA chops are a lot better), so perhaps educating myself about the personal.xlsb file is the way to go (as I've already got the Excel VBA code I need...just need to locate/store it somewhere that can be called from any excel spreadhseet).

(Uncle Gizmo, my question more relates to storing/called VBA on an exported Excel doc ...vs. getting it to Excel from Access ...unless I misinterpreted your links?)
peskywinnets is offline   Reply With Quote
Old 01-27-2019, 05:56 AM   #5
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,850
Thanks: 411
Thanked 691 Times in 670 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Exporting as Excel file from Access (wanting to run an Excel macro)

Just recorded this to bring a table from an Access DB to Excel
Transactions was the table I used, DB is obvious.

Code:
Sub AccessInput()
'
' AccessInput Macro
'

'
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\Paul\Documents\SSAFA\SSAFA.mdb;Mode=Share Deny " _
        , _
        "Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:En" _
        , _
        "gine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB" _
        , _
        ":New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on" _
        , _
        " Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False" _
        ), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("Transactions")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = "C:\Users\Paul\Documents\SSAFA\SSAFA.mdb"
        .ListObject.DisplayName = "Table_SSAFA"
        .Refresh BackgroundQuery:=False
    End With
End Sub
This would be the way I would approach it. Remove hard coded values for variables.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 01-27-2019, 06:30 AM   #6
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,168
Thanks: 495
Thanked 904 Times in 856 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Exporting as Excel file from Access (wanting to run an Excel macro)

Quote:
Originally Posted by peskywinnets View Post


(Uncle Gizmo, my question more relates to storing/called VBA on an exported Excel doc ...vs. getting it to Excel from Access ...unless I misinterpreted your links?)
Bob Larson's code allows you to format the excel sheet from MS Access. It contains examples.

Also you can use it as is, just feed it the SQL Statement that returns the records you want to export.

I'm thinking it avoids having to call code in Excel.

Sent from my SM-G925F using Tapatalk
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 01-27-2019, 09:25 AM   #7
peskywinnets
Newly Registered User
 
Join Date: Feb 2014
Location: London, UK
Posts: 517
Thanks: 278
Thanked 5 Times in 5 Posts
peskywinnets is on a distinguished road
Re: Exporting as Excel file from Access (wanting to run an Excel macro)

Quote:
Originally Posted by Gasman View Post
Just recorded this to bring a table from an Access DB to Excel Transactions was the table I used, DB is obvious.

This would be the way I would approach it. Remove hard coded values for variables.?
That's very good of you....(having spent a reasonable while coding VBA in Access now, a lot of the syntax looks surprisingly foreign to me!)

What changes would I need to pull the output of an Access query? (as ultimately that's what I'm exporting from Access)

peskywinnets is offline   Reply With Quote
Old 01-27-2019, 09:45 AM   #8
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,850
Thanks: 411
Thanked 691 Times in 670 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Exporting as Excel file from Access (wanting to run an Excel macro)

Just pick the required object.

Record your own macro by walking through the steps. That is all I did. Then tweak as needed.
It's the same VBA, just some different properties/methods.?

Be aware that the macro recorder does generate a fair bit of code that is not all really needed, but does not do any harm, least not so I have ever noticed.

I would have thought that you would get most of what you need just by changing the .CommandText and .SourceDataFile properties.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
peskywinnets (01-27-2019)
Old 01-27-2019, 01:52 PM   #9
peskywinnets
Newly Registered User
 
Join Date: Feb 2014
Location: London, UK
Posts: 517
Thanks: 278
Thanked 5 Times in 5 Posts
peskywinnets is on a distinguished road
Re: Exporting as Excel file from Access (wanting to run an Excel macro)

Many thanks for your input - very helpful.

peskywinnets is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Access 2010 report, exporting to excel produces blank xls file eddyfuente Reports 5 06-27-2014 09:54 AM
Exporting Access Query to Excel (into a temp file) ecrespol Modules & VBA 1 08-09-2011 01:10 AM
Help Access 2007 macro to open a excel file dinfinity Macros 3 03-03-2011 07:17 AM
Access VBA to run a macro when cell in excel file is activated JFKJr General 2 07-28-2008 04:40 AM
Running excel macro after exporting data into Excel dwhite Modules & VBA 2 12-23-2002 07:11 AM




All times are GMT -8. The time now is 11:16 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World