Solved: Pulling data out of Excel

[CreganTur]

I don't know if this is possible, but I really hope that it is.

Basic concept is that I want to use a Choose File dialogue window so User can target an excel spreadsheet that they want to load into a database (this is the easy part). Here's the challenge:

The spreadsheet has data in rows A - M, but the database only needs the data in rows B - F & M. Is there a way to pull this data from the specified rows in a spreadsheet and add them into an existing table?

[herzberg]

I'm doing something similar for one of my databases. Not sure if this helps, but here goes:

1. Create a linked table with the source as the Excel file; let's call this linked table "Table1"
2. Have an append query to select specific columns (in my case, I select the first 2 and last 2 columns) from Table1 and append them to another table, Table2
3. Convert the above 2 steps into a macro and execute it everytime I want Table2 to be updated.

A slight difference is that I place the updated Excel file in the same directory and name it the same everytime, instead of having a dialog popup to choose the file.

[CreganTur]

1. Create a linked table with the source as the Excel file; let's call this linked table "Table1"
2. Have an append query to select specific columns (in my case, I select the first 2 and last 2 columns) from Table1 and append them to another table, Table2
3. Convert the above 2 steps into a macro and execute it everytime I want Table2 to be updated.

That's a great idea- it's a simple and effective way to accomplish my goals.


A slight difference is that I place the updated Excel file in the same directory and name it the same everytime, instead of having a dialog popup to choose the file.

I see why you have to do this- because a Linked table is set to a static Filepath.

I'm going to do some research to see if it is possible to define a linked table via VBA. If it is possible, then using the dialog would be simple- save the result of the dialog box (should be filepath of chosen file) as a str variable, and then have the linked table code set to that same variable.

If anyone already knows how to set a linked table via VBA, would be most appreciative: pray2:

[CreganTur]

Okay, I've made a small breakthrough. It is possible to use VBA to create a linked table. So far I've only got it setup so that it will link to a table where the filepath is static. This example has the code being triggered by an Event (click), and is also specific to Excel files.

Private Sub Command0_Click()
Dim myDB As DAO.Database, tbl As TableDef
Dim stSource As String, stConnect As String
Set myDB = CurrentDb()
stSource = "[Worksheet name]$"
stConnect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=[Filepath]"
Set tbl = myDB.CreateTableDef("[Desired Name]")
tbl.Connect = stConnect
tbl.SourceTableName = stSource
myDB.TableDefs.Append tbl
LinkExcel = True
Exit_:
Set tbl = Nothing
Set myDB = Nothing
End Sub

[Worksheet name] is the name of the acutal worksheet inside the Excel file. Note: you MUST have the "$" after the name of the worksheet!

[Filepath] is the filepath of the Excel file

[Desired Name] is the name you want the linked table to have inside your database.

[CreganTur]

I've solved this problem- it involves:

using a choose file dialog box
creating a linked table with the chosen file
running an update query on the new linked table to insert data into existing table
releasing the linked tableHere's the code:


DoCmd.SetWarnings False '<<<only do this after you have tested code
'to ensure that it works the way you want it to
Dim strFilepath As String
'You must have a reference to Microsoft Office Object Library v11 or v12
Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen
.AllowMultiSelect = False '<<<only allows single file to be chosen
.Title = "Please select Results file to load"
.Filters.Clear
.Filters.Add "Excel Files", "*.XLS"

If .Show = -1 Then

'Step through each string in the FileDialogSelectedItems collection.
For Each vrtSelectedItem In .SelectedItems


strFilepath = vrtSelectedItem '<<<get filepath of excel file

'MsgBox "Imported Database Names from: " & vrtSelectedItem
Next vrtSelectedItem

Else
End If
'make the selected Excel file into a Linked Table
Dim myDB As DAO.Database, tbl As TableDef
Dim stSource As String, stConnect As String
Set myDB = CurrentDb()
stSource = "[worksheetName]$" '<<<see Note1
stConnect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strFilepath
Set tbl = myDB.CreateTableDef("mySheet") '<<<assigns name of link tbl
tbl.Connect = stConnect
tbl.SourceTableName = stSource
myDB.TableDefs.Append tbl
LinkExcel = True
Exit_:
Set tbl = Nothing
Set myDB = Nothing
End With
'run Update query to pull data from Linked Table into tblResults
Dim sqlUpdate As String
sqlUpdate = [SQL code] '<<<see Note2
DoCmd.RunSQL sqlUpdate
'release linked table
DoCmd.DeleteObject acTable, "mySheet"
DoCmd.SetWarnings True
End Sub

Note1: You must provide the actual name of the worksheet inside the excel file you are opening (all of the files I'm loading have the same worksheet name, so I was able to hardcode it)- you MUST have the "$" (sans quotes) at the end of the worksheet name

Note2: Here is where you would enter the SQL code you want to use to load the data from the temporary Link Table into another table. I use an Update query to move this data into an existing table.