Exporting as Excel file from Access (wanting to run an Excel macro)

[peskywinnets]

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!)

 

[Gasman]

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

 

[Uncle Gizmo]

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-table-or-query-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.

 

[peskywinnets]

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)

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?)

 

[Gasman]

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

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.?

 

[Uncle Gizmo]

(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

 

[peskywinnets]

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)

 

[Gasman]

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.?

 

[peskywinnets]

Many thanks for your input - very helpful.