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

 

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

 

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