0

I am trying to automate a process of exporting data from a database in MS Access to excel using VBA. When I do it regularly (excuse my lack of proper verbiage as I am a complete newb at this), I start by creating a query linking two tables. I double click the asterisk to show the tables. I add one of the column headers as a field in the query and add the criteria I need. Then I run the query and export it to excel.

I have tried using Google to solve my issue and I have tried to copy and paste the SQL from the query into VBA. I think the latter could work but it is missing the first step I think but I am not sure what that step is.

Edit: This is one attempt I've made with help from the internet.

Sub createQry()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim qdf As DAO.QueryDef
    Dim newSQL As String

    newSQL = "Select * From [(MR)Events2025] And [(MR)EventMemo2025]     WHERE [EvtDate]= >=#1/1/2022# And <=#1/31/2022#"


End Sub

I want to make it to where I can click a button in excel and it run the process of creating the query in access and then exporting it to excel. The file changes monthly with updated data and I would like to not have to do the same thing every month and just click a button to get the data I want. If it were all in excel I would be fine because of the record macro feature but it doesn't seem to work outside of excel.

I guess what I want the code to do is open the access database when I click a button in excel, create the query in access (this includes copying a date range from the cell I have selected and pasting it in the criteria portion of the query, or just having that part of the query equal the selected cell in excel), and export the data from the query to excel. I can figure the other stuff I want to do via the macro recorder.

The below creates the query for me, now I have to export it to excel.

Sub CreateQueryDefX()
 
   Dim dbsAssetManagement As Database
   Dim qdfTemp As QueryDef
   Dim qdfNew As QueryDef
 
   Set dbsAssetManagement = OpenDatabase("C:(deleted file location for privacy)AssetManagement.accdb")
 
   With dbsAssetManagement
     
      Set qdfNew = .CreateQueryDef("NewQueryDef", _
         "SELECT [(MR)Events2025].*, [(MR)EventMemo2025].* FROM [(MR)Events2025] INNER JOIN [(MR)EventMemo2025] ON [(MR)Events2025].MCN = [(MR)EventMemo2025].MCN_ID WHERE ((([(MR)Events2025].EvtDate) >=#1/1/2022# And ([(MR)Events2025].EvtDate)<=#1/31/2022#))")
     
   End With
 
End Sub
Darth Slader
  • 1
  • 1
  • 1
    Edit question to show the VBA attempt to build SQL statement. Why do you need to create query for each process? Why not build query object once? What changes each time? By 'run' you mean switch to Datasheet view? Can certainly build VBA statement in SQL and modify a query object using QueryDefs. Could also just build query object that references controls on form for input of filter criteria (a parameterized query). Review http://accessmvp.com/KDSnell/EXCEL_Export.htm – June7 Jan 23 '23 at 20:26
  • Easiest for me and most flexible is to build a query totally within the Access database that delivers data exactly as I need it, save it in Access as a QueryDef, then go into Excel and Import the data from the Access DB QueryDef as a table in Excel. If your underlying data changes you can simply Refresh in Excel to rerun your saved query. [no VBA used at all unless you want to automate the refresh each time you start EXCEL.] – Chris Maurer Jan 23 '23 at 21:08
  • Please provide enough code so others can better understand or reproduce the problem. – Community Jan 24 '23 at 04:22
  • See this: https://stackoverflow.com/questions/74078962/ms-access-vba-sql-append-query-returns-syntax-error-when-appending-string-with-s/74079504#74079504 – Kostas K. Jan 24 '23 at 09:02

2 Answers2

0

You can save the query/queries you want to export and then in VBA export them to files using the TransferSpreadsheet method

See https://learn.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet

Example:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, 
"staff_list_with_grouping", "C:\test\test.xlsx", True

Alternatively you can do the following steps in VBA (1) create an Excel object , (2) open a blank workbook, (3) open the query you want to export, (4) copy its data (5) paste the data in the Excel workbook, (6) save the workbook , (7) reclose your query

Example of code exporting a query to an existing Excel template:

Sub ExportSearchResults()


DoCmd.OpenQuery ("MyQuery")
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy


Dim xlo As New Excel.Application

xlo.Workbooks.Add (getTemplateFolder & "ExportTemplate.xlsx")

xlo.ActiveSheet.Range("D1").Value = Now()
xlo.ActiveSheet.Range("A4").Select

xlo.ActiveSheet.PasteSpecial Format:="Unicode Text"

xlo.ActiveSheet.Cells.Select
xlo.Selection.ColumnWidth = 30
xlo.Selection.RowHeight = 15
xlo.ActiveSheet.Cells.Select
xlo.ActiveSheet.Cells.EntireColumn.AutoFit
xlo.ActiveSheet.Cells.Select
xlo.ActiveSheet.Cells.EntireRow.AutoFit



xlo.Visible = True
xlo.UserControl = True
xlo.WindowState = -4137 
xlo.Range("A1").Select


End Sub
Gert De Wilde
  • 1
  • 1
  • This would probably work but I need the query to be in the vba so that when the new file comes in I can replace it and not have to import a query or change the file name/path in the code. I didn't have that in my original question so I apologize. This is my first stacks question. – Darth Slader Jan 24 '23 at 15:00
  • You can modify the SQL content of a saved query in your VBA code using the DAO.QueryDef object. I am somewhat confused if you say "the new file comes in": are you talking about an Access or an Excel file? – Gert De Wilde Jan 24 '23 at 18:42
  • Access file. I receive an access database every month with data added to it from the previous month. I want to be able to rename that file to the one my code calls to and then click a button and it pull all the data I want. – Darth Slader Jan 25 '23 at 15:21
  • I would tend to create the VBA procedure in a separate MS Access application linked to the relevant tables in your monthly updated file (which you can overwrite each time). Then create saved queries to collect the data you want to export from the monthly file. Finally export this/those queries whereever you want them with the procedures as described. Depending on how your monthly file is delivered you could also automate the rename/overwrite process of your monthly deliveries. – Gert De Wilde Jan 25 '23 at 16:53
0

I was originally trying to do this in Access but I was eventually able to find a video for VBA in excel. It pulls all the data that I want.

Sub getDataFromAccess()
' Click on tools, references and select
' the Microsoft ActiveX Data Objects 2.0 Library

Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

' Database path info
DBFullName = "C:(deleted file location for privacy)AssetManagement.accdb"

' Open the connection
Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Connect

'Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
'Filter Data
Source = "SELECT [(MR)Events2025].*, [(MR)EventMemo2025].* FROM [(MR)Events2025] INNER JOIN [(MR)EventMemo2025] ON [(MR)Events2025].MCN = [(MR)EventMemo2025].MCN_ID WHERE ((([(MR)Events2025].EvtDate) >=#1/1/2022# And ([(MR)Events2025].EvtDate)<=#1/31/2022#))"

.Open Source:=Source, ActiveConnection:=Connection

'MsgBox "The Query:" & vbNewLine & vbNewLine & Source

'Write field names
For Col = 0 To Recordset.Fields.Count - 1
 Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next

'Write Recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With
ActiveSheet.Columns.AutoFit
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing


End Sub
Darth Slader
  • 1
  • 1