How to: Button and Macro to Run Module (1 Viewer)

tanyamc

Registered User.
Local time
Today, 07:19
Joined
Mar 7, 2019
Messages
43
I figured out the code I need to export several queries from Access to an Excel workbook with multiple worksheets, one per query.

I can create the workbook if I open the code and click 'run'.

I want to create a form with a button that when clicked, runs a macro that runs the module to create the workbook.

I would also like the worksheets to be formatted as a table so they are sortable when the Excel file is opened by a user.

I have made several attempts based on Google search results, but still can't figure it out. This is my first attempt at something like this. (I was using Excel to connect to the queries, but that process is very buggy on our system so trying this instead.)

Here is the module which is named 'Export_Results':

Private Sub Command37_Click()
'Exports each of the queries into a multi-tabbed spreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Agency_Ave_miles_post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "State_Ave_Miles_post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AveByAgencyByState", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_TotalAgency", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_TotalState", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week1_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week2_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week3_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week4_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week5_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week5_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week7_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Week8_Post", "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls", True


'Opens the file you just created
Application.FollowHyperlink "H:\Documents\AH_Connect\Winter Olympics\2020\Weekly_Results_Post.xls"


End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:19
Joined
Oct 29, 2018
Messages
21,357
Hi. Have you looked into Excel Automation? Just curious...
 

namliam

The Mailman - AWF VIP
Local time
Today, 14:19
Joined
Aug 11, 2003
Messages
11,696
any excel sheet is sortable no need for it to be a "table"

creating a form shouldnt be to hard to stick this code behind, how far have you got and what problems are you experiencing?
 

tanyamc

Registered User.
Local time
Today, 07:19
Joined
Mar 7, 2019
Messages
43
If you format the sheets as tables, then all the data sorts as one, avoiding the pitfalls of people sorting a column instead.

In Access, on a form, I want to create a button that when clicked will run the module and create the Excel file. I know how to create the form, and the button, but the OnClick is where I get stuck as to how to make that run the module.

I tried using Excel query/connection to read Access instead but as I said, it's very buggy/unreliable in our environment.
 

namliam

The Mailman - AWF VIP
Local time
Today, 14:19
Joined
Aug 11, 2003
Messages
11,696
If you format the sheets as tables, then all the data sorts as one, avoiding the pitfalls of people sorting a column instead.
Nonsense, works with any dataset just fine even if not a table

I will agree willingly there are advantages to working with a sheet formatted as a table, however 99% of all things work just fine in any sheet.

Easiest way to do this is to "simply" export your data as a semi-column seperated text file (or any other seperator).
Then have the user open an excel sheet containing below code
Code:
Sub Macro1()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Yourpath\Yourfile.csv" _
        , Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "YourFile"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.QueryTables("YourFile").Delete
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$AZ"), , xlYes).Name = "YourTable"
End Sub

Have them press the button and presto your golden, can be done from access as well but much more convoluted.

Also if you like you can extract the data from inside the excel as well, thus making it a one button job from excel.
 

tanyamc

Registered User.
Local time
Today, 07:19
Joined
Mar 7, 2019
Messages
43
Thank you. From my experience with my user group, the table formatting reduces my time spent troubleshooting.

I am generating the report at least weekly, and it may not always be me doing it, so I need the button in the database. I have tried calling the database from Excel and it never works correctly after the initial build. I believe there are security issues or something I can't see/control and I wasted a lot of time on that method that I could have used to create the 12 sheet workbook manually instead. So I went to Access export instead.

I don't want to touch the Excel file after it is created. I just want to download updated data, click to create the Excel file, and then upload the file for the users.
 

Users who are viewing this thread

Top Bottom