VBA, Integrating Excel and Access query/export

[kestefon]

I'm working on a project that uses Excel for formatting and then Access for manipulating the data. I have the Excel VBA and my Access queries done, and now I'm just trying to figure out how to combine the two. The first step, which I've already done, is formatting raw data in Excel with VBA. It results in a sheet with the variable name "data_CurrentMonthYear", i.e. "data_Nov2013, data_Dec2013, etc. I'm trying to:



1) Create a new Access database with "data_Nov2013", as well as the table and query found in the attached file ("dataSample.accdb"). Ideally, I'd like to store that dataSample table and query within the VBA itself. Note, I would also like the SQL code in the query to account for the variable table name (i.e. "data_Nov2013", etc.).

2) Save the resulting database as an Access file.

3) Export the resulting Access query to a pipe-delimited text file. (e.g. "query_data_Nov2013.txt")

Step 1 is where I'm really stumped...I should be able to figure out the remaining steps, but any assistance on those would be appreciated as well. Here's the attachment: dataSample.accdb

[June7]

You want to create a new Access database for each month/year? Why not just all in one database?

This process is moving data from Excel to Access to text?

[kestefon]

Yeah, a separate Access file for each month.

Basically, to keep things simple, I'm trying to learn how to do the following:

1) Export/save an Excel sheet ("data_Nov") as an Access table.
2) Perform a query on that "data_Nov" table by integrating the SQL within the VBA code itself.

I should be able to figure out the rest on my own.

[June7]

1) you want code behind Excel to create Access file and push data out to a new table in that new file

2) create query in the new Access file

3) export query from Access to text file

And that's keeping things simple?? It can be done but I wouldn't call it simple.

And there's more!!?

[kestefon]

Haha...well, it's advanced VBA I suppose, but I don't think the code will be overly complex. This, for instance, will load an existing Access database, run a query, and copy it a sheet in your Excel workbook.

That's not really what I'm trying to do in this case, but it should give you an idea of the type of code I'm trying to write. My problem is I'm not comfortable with Access VBA at all. :/

Sub RunAccessQuery()


'Step 1: Declare your variables
   Dim MyDatabase As DAO.Database
   Dim MyQueryDef As DAO.QueryDef
   Dim MyRecordset As DAO.Recordset
   Dim i As Integer


'Step 2: Identify the database and query
   Set MyDatabase = DBEngine.OpenDatabase _
   ("[insert filename]")
   Set MyQueryDef = MyDatabase.QueryDefs("[insert query name]")


'Step 3: Open the query
   Set MyRecordset = MyQueryDef.OpenRecordset


'Step 4: Clear previous contents
   Sheets("Main").Select
   ActiveSheet.Cells.ClearContents


'Step 5: Copy the recordset to Excel
   ActiveSheet.Range("A2").CopyFromRecordset MyRecordset


'Step 6: Add column heading names to the spreadsheet
   For i = 1 To MyRecordset.Fields.Count
   ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
   Next i


End Sub

[June7]

Did I correctly outline the process you want?

I have code that creates an Access file and exports data but I do this behind Access, not Excel.

I have seen code that will take Excel rows and build a recordset, maybe I can find it again, otherwise, passing data from Excel cells to Access table is not so simple.

[kestefon]

Hm...could I take a look at that? It might be helpful in any case. Thanks!

[June7]

Code to create Access file and tables:

Public Sub ConstructionExtract()

'exports data to ConstructionExtract Access file
'copies file to zip folder
'opens Outlook object and attaches file to msg and sends

Dim strZip As String
Dim strExtract As String
Dim Catalog As Object
strZip = gstrBasePath & "Program\Editing\ConstructionExtract.zip"
strExtract = gstrBasePath & "Program\Editing\ConstructionExtract.accdb"
'create new database
Set Catalog = CreateObject("ADOX.Catalog")
Catalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strExtract & ";"
Set Catalog = Nothing
'import tables to the ConstructionExtract Access file
CurrentDb.Execute "SELECT * INTO Bituminous IN '" & strExtract & "' FROM ConstructionBIT;"
CurrentDb.Execute "SELECT * INTO BituminousMD IN '" & strExtract & "' FROM ConstructionBMD;"
CurrentDb.Execute "SELECT * INTO Concrete IN '" & strExtract & "' FROM ConstructionCONC"
CurrentDb.Execute "SELECT * INTO Emulsion IN '" & strExtract & "' FROM ConstructionEMUL;"
CurrentDb.Execute "SELECT * INTO PGAsphalt IN '" & strExtract & "' FROM ConstructionPG;"
CurrentDb.Execute "SELECT * INTO SoilsAgg IN '" & strExtract & "' FROM ConstructionSA;"
CurrentDb.Execute "SELECT * INTO SampleInfo IN '" & strExtract & "' FROM ConstructionSampleInfo;"

'create empty zip folder
'found this on web, no idea what the Print line does but if it isn't there, this won't work
Open strZip For Output As #1
Print #1, "PK" & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
'copy file into zip folder
Dim objApp As Object
Set objApp = CreateObject("Shell.Application")
'variable for source file doesn't seem to work in this line
'also double parens not in original code but won't work without
objApp.NameSpace((strZip)).CopyHere gstrBasePath & "Program\Editing\ConstructionExtract.accdb"

'open Outlook, attach zip folder, send e-mail
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "email address here"
    ''.cc = ""
    ''.bcc = ""
    .Subject = "Laboratory Data"
    .HTMLBody = "Construction data extract: " & Now
    .Attachments.add (strZip)
    .DeleteAfterSubmit = True 'to not save in sent bin                        
    ''.Display
    .Send
End With

'delete zip folder and ConstructionExtract.accdb
Kill strZip
Kill strExtract
CurrentDb.Execute "UPDATE Updates SET ConstructionExtract=#" & Date & "#"

End Sub