Export VBA SQL String to Excel withing a Form

Jaryd

Board Regular
Joined
Apr 17, 2013
Messages
123
Hello,

I have been trying this for a while with no success. I was hoping for some assistance. I am going to sleep now, but will answer any followup questions in the morning.

I have a form with some controls to get criteria.
I am trying to complete the part of this process where a query I create in vba as a string with variable data gets exported to Excel.
The control values make up most of the query including which table to pull from.

Any help would be great. Thanks.


Code:
Private Sub Command133_Click()

Dim Table_Name As String
Dim Table_Type As String
Dim strSQL As String


Select Case Me.Report_Type.Value
    Case 1
        Table_Type = "01 Month History"
    Case 3
        Table_Type = "03 Month History"
    Case 6
        Table_Type = "06 Month History"
    Case 12
        Table_Type = "12 Month History"
    Case 2
        Table_Type = "02 Month History"
    Case 4
        Table_Type = "Details"
    Case 20
        Table_Type = "Totals"
    Case 47
        Table_Type = "AA History"
    Case 519
        Table_Type = "BB History"
    Case 1519
        Table_Type = "CC History"
    Case 1619
        Table_Type = "DD History"
End Select






Table_Name = "Table" & Me.Report_Name_Toggle.Value & " " & Table_Type




strSQL = "SELECT * FROM [" & Table_Name & "] WHERE [Report Year] = '2015';"


'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExce8, _
   Qry, "C:\Users\Jaryd\Downloads\FileName.xls", True


'DoCmd.OutputTo acOutputQuery, strSQL, "ExcelWorkbook(*.xlsx)", "", False, "", , acExportQualityPrint
End Sub
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Ignore this...

Basically I had an extra character in some text.

There is plenty of info out there to do this. Different then in the code above I just needed to set a currentdb then db.createquerydef. I would prefer to do with just the SQL string though.
 
Upvote 0
Possibly for future consideration, don't store your data in so many different tables, which makes reporting easier (you would only need to change where criteria, not the table sources)
 
Upvote 0
Possibly for future consideration, don't store your data in so many different tables, which makes reporting easier (you would only need to change where criteria, not the table sources)


Well in this situation I have 4 different reports, each with 1-4 variations. So I actually have a separate DB for each report, and then a separate table for each variation. I originally had all the tables in 1 report, but it got over 2gb. So I want this form to query the info from a linked DB that will be on a network. I'm hoping I can eventually make it work.

I already build and rebuilt a little import tool that takes the new data and adds to the right location.

I know I should probably move this stuff to a sql server, but I am actually kinda new still so I am just working with what I got.
 
Upvote 0
If your db is large be sure to compact and repair regularly. Sometimes the extra size in an MSAccess file is just unused space. Having extra databases as well as extra tables doesn't fill me with enthusiasm so think about where this is going as well as what you have to do now - I agree that if you have so much data you need numerous database files then access is probably not your best bet.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top