Ok, here is how I would do it. It may not be the fastest or best way, but using this method has certainly worked for me.
First task is to get your Excel data in a format that is friendly for Access to import and work with. I created this simple macro to do that. Put this macro on a standard module in your workbook, and save the workbook. Don't run the macro, Access will run it for you.
Public Sub PrepForImport()
Dim i As Integer, j As Integer
Dim lngNR As Long
Dim w As Object, ws As Object
For Each w In ThisWorkbook.Worksheets
Application.DisplayAlerts = False
If w.Name = "ForImport" Then w.Delete
Application.DisplayAlerts = True
Next w
Worksheets.Add before:=Sheets(1)
ActiveSheet.Name = "ForImport"
With Sheets("ForImport")
.Cells(1, 1).Value = "Date"
.Cells(1, 2).Value = "Region"
.Cells(1, 3).Value = "Cansim"
.Cells(1, 4).Value = "VariableName"
.Cells(1, 5).Value = "MonthlyValue"
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "ForImport" And ws.Name <> "Index" And ws.Name <> "Other Source (BMO)" And ws.Name <> "Insert" Then
For i = ws.Cells(1, 3).End(xlDown).Row + 1 To ws.Cells(Rows.Count, 3).End(xlUp).Row
For j = 5 To ws.Cells(ws.Cells(1, 3).End(xlDown).Row, ws.Cells(ws.Cells(1, 3).End(xlDown).Row, Columns.Count).End(xlToLeft).Column).Column
lngNR = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(lngNR, 1).Value = ws.Cells(ws.Cells(1, 3).End(xlDown).Row, j).Value
.Cells(lngNR, 2).Value = ws.Name
.Cells(lngNR, 3).Value = ws.Cells(i, 4).Value
.Cells(lngNR, 4).Value = ws.Cells(i, 2).Value
.Cells(lngNR, 5).Value = ws.Cells(i, j).Value
Next j
Next i
End If
Next ws
End With
End Sub
Ok, I tried like crazy to upload the database, but I can't seem to do it. Here is the added code:
Option Compare Database
Option Explicit
Public Sub User_Import()
'Declare local variables:
Dim fd As FileDialog 'Need to make sure Microsoft Office X.0 Object Library is referenced.
Dim strFile As String
Dim var As Variant
Dim XL As Object
Dim Table As Object
'Initialize variables:
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'Set up file dialog for user to select the file to import. You could also hardcode this, or store the
'file and path in a table with a button allowing the user to change that path. You can display the
'path with a label and a dlookup.
With fd
.AllowMultiSelect = False
.Title = "Select the Excel file to import into this database."
.Filters.Add "Excel Files (*.xls)", "*.xls"
'Check if the user selects Cancel
If .Show = 0 Then Exit Sub
For Each var In .SelectedItems
strFile = var
Next
End With
'Clear the fd variable.
Set fd = Nothing
'Open an instance of Excel.
Set XL = CreateObject("Excel.Application")
'Don't let the user see it.
XL.Visible = False
'Open the Excel workbook to run the macro that will prep the workbook for import.
XL.Workbooks.Open strFile
'Run the macro to prep the workbook. This will put workbook in a format for upload.
XL.Application.Run "PrepForImport"
'Save and close the workbook.
XL.activeworkbook.Close True
'Close and exit Excel
XL.Quit
'Clear the Excel Variable
Set XL = Nothing
'Clear any data in the table that we will be importing the new data into.
'First, turn of alerts so the user isn't asked to confirm record changes.
DoCmd.SetWarnings False
'Run the sql to clear the table.
DoCmd.RunSQL "DELETE * FROM tbl_Import"
'Turn warnings back on.
DoCmd.SetWarnings True
'Now import the fresh data into the table.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tbl_Import", strFile, True
'Now we will run a query to get the data into your desired format into the Statistics_Canada_CPI table.
'First turn off alerts
DoCmd.SetWarnings False
'Delete any error tables.
For Each Table In CurrentDb.TableDefs
If Left(Table.Name, 12) = "ImportErrors" Then Table.Delete
Next Table
'Next run the query.
CurrentDb.QueryDefs("qry_TransferImport").Execute
'Turn alerts back on.
DoCmd.SetWarnings True
'Let the user know the import was successful.
MsgBox "Import Successful", vbOKOnly
End Sub
I had to use the Excel tab names to identify which province the data was coming from, so I had to add a table to link the tab names with the province ID numbers, see a screenshot. I also added a table to temporarily hold the imported data, as I described earlier. This captured all the data in your workbook except the Other Source (BMO) data. You'll want to either alter the Excel macro or define a new Access import to help you get that info into Access. I've also commented the Access data pretty well so you should know exactly what is happening at each step.
Here is the Query:
INSERT INTO Statistics_Canada_CPI ( MonthYear_Name, RegionID, Cansim_NUmber, Variable_Name, Monthly_Value )
SELECT tbl_Import.Date, Regions.RegionID, tbl_Import.Canism, tbl_Import.VariableName, tbl_Import.MonthlyValue
FROM (Regions INNER JOIN tbl_RegionTransfers ON Regions.RegionID = tbl_RegionTransfers.Region) INNER JOIN tbl_Import ON tbl_RegionTransfers.RegionFromExcel = tbl_Import.Region;
Let me know if you have any questions.
LinkBack URL
About LinkBacks
Register To Reply
). I have two tables, a Regions table and a Statistics_Canada_CPI table. I have populated the Regions table but now I need to populate the Statistics_Canada_CPI table but as seen in the previous Cdn&ProvCPI.zip file that I uploaded, there is a lot to load. My question now becomes, how do I do this?
I was worried that I would have to enter each value individually so your way will be best I'm sure. I tried to import the data but the Primary key index in table Statistics_Canada_CPI wouldn't auto index doing it that way.
Originally Posted by Mordred
Almost there!
Bookmarks