Linking a query to an Excel Spreadsheet

[mattster1010]

Good Afternoon,

Does anyone know how to link an MS access query to an Excel spreadsheet?

Cheers,

Mattster

[CreganTur]

The following code is from a KB entry I wrote that's awaiting approval, It will allow you to choose an Excel spreadsheet via a file dialog menu, it will get the filepath and worksheet names of the spreadsheet, and then it will run some validation to make certain that the correct field names exist in the field table. and it will then make it into a linked table. Once it's a linked table, you can run SQL against it. It is set right now to allow the user to select multiple spreadsheets- it will check all worksheets in each spreadsheet.

How to use:

Create a button on a form
On the button's properties sheet click on the Event tab
Click the On Click event
Click the Build button "..."
Choose Code Builder if prompted
Copy the code above
Paste the code between the Sub and End Sub lines
Go to the Validation section of code
Change the field names to exactly match fields in your Excel spreadsheet
Add as many field validation checks as needed
Adjust the "If Not strExists = 3" to equal the number of fields being validated
Write a new Update Query after the "sqlUpdate = " variable
Be sure to enclose Update Query in quotation marks
Click Tools
Click References
Choose Microsoft Office 11.0 Object Library, or Microsoft Office 12.0 Object Library (either will work)
Choose Microsoft Excel 11.0 Object Library
Click OK
Optional: change MsgBox messages to match your processTesting the Code:

Be sure you have a table created that the spreadsheet data can Update to
Open the form with button that this code is behind
Click the button
A file dialog window will appear
Navigate to the desired spreadsheet to load into database
Select the desired spreadsheet
Click Open
Successful Load message should appear
Close Form
Open Updated table
Records from Spreadsheet should appear in tableHTH

'Turn off warnings <<< reduces chance for User to cause a problem
DoCmd.SetWarnings False

Dim strFilepath As String
Dim strSheetName As String
Dim instrFile As String
Dim instrItem As String
Dim dlgOpen As Office.FileDialog
Dim vrtSelectedItem As Variant
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen

.AllowMultiSelect = True '<<<User can select multiple files | False for single file
.Title = "Please select file to load" '<<<Title text for window
.Filters.Clear '<<<Removes any old File Dialog filters
.Filters.Add "Excel Files", "*.XLS" '<<<sets filter to Excel files

If .Show = 0 Then '<<< if User presses Cancel then Sub ends
Exit Sub
Else
'Will loop through all files selected by the Dialog window
For Each vrtSelectedItem In .SelectedItems
strFilepath = vrtSelectedItem '<<<Set filepath to Variable

'--------------------
'this code gets the name of each worksheet in the workbook
'every worksheet in the workbook will be evaluated
Dim excelname As String, AppExcel As New Excel.Application, Wkb As Workbook, Wksh As Worksheet
Dim obj As AccessObject, dbas As Object, tempTable As String, spaceIn As Integer
Dim count As Integer

excelname = strFilepath
Set Wkb = AppExcel.Workbooks.Open(strFilepath)
For Each Wksh In Wkb.Worksheets

'--------------------
'make the selected Excel file into a Linked Table

Dim myDB As DAO.Database, tbl As TableDef
Dim stSource As String, stConnect As String
Dim LinkExcel As Variant

Set myDB = CurrentDb()
stSource = Wksh.Name & "$"
stConnect = "Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & strFilepath

'Make chosen Excel file into a Linked Table titled mySheet
Set tbl = myDB.CreateTableDef("mySheet")
tbl.Connect = stConnect
tbl.SourceTableName = stSource

myDB.TableDefs.Append tbl

LinkExcel = True

'--------------------
'Validation: check to see if required fields exist in linked table
Dim dbs As Database
Dim tdf As TableDef
Dim fld As Field, fldnew As Fields
Dim a, b
Dim strExists As String

strExists = 0 '<<<Set initial value of validation variable - Used later
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("mySheet")

'counts the number of fields in the table
With tdf
b = .Fields.count

'cycles through the linked table's fields and displays the names
'they need to match the linked tables field names exactly
For a = 0 To b - 1 '<<<because there are some hidden fields have to -1 sometimes -2
'When a field is shown to exist it increases the value of strExists by 1
If tdf.Fields(a).Name = "FieldName1" Then
strExists = strExists + 1
ElseIf tdf.Fields(a).Name = "FieldName2" Then
strExists = strExists + 1
ElseIf tdf.Fields(a).Name = "FieldName3" Then
strExists = strExists + 1
End If


Next a
'If strExists does not equal the number of fields you are validating (cont)
'then it means 1 or more fields are missing (cont)
'If a required field is missing then cancel Sub and release linked table
End With
If Not strExists = 6 Then
MsgBox "The chosen file does not match the required format:" & vbCrLf & "(" _
& strFilepath & ")" & vbCrLf & "Worksheet Name: " & Wksh.Name & vbCrLf _
& "Please choose a different file to load." _
& vbCrLf, vbExclamation + vbOKOnly, "Load Error"
DoCmd.DeleteObject acTable, "mySheet" '<<<Release Linked Table
Goto NextItem
End If

'--------------------
'run Update query to pull data from Linked Table into tblResults
Dim sqlUpdate As String

'Hardcoded SQL statement
'Use Update query to add data from linked table "mySheet" into existing Table
sqlUpdate = "INSERT INTO tblName ( Fields In Table to Update) SELECT mySheet.FieldName FROM mySheet;"

'DoCmd.RunSQL sqlUpdate '<<<Runs Update query

'release linked table because we are done with it
DoCmd.DeleteObject acTable, "mySheet"

'Let the User know the operation was successful
Beep
MsgBox "Employee data has been successfully loaded into the database.", vbOKOnly, _
"Successful Load"

NextItem:
Next Wksh
Wkb.Close
AppExcel.Quit
Set Wkb = Nothing
Set AppExcel = Nothing

Next vrtSelectedItem
End If
End With

'Turn warnings back on
DoCmd.SetWarnings True

[rangudu_2008]

You can also refer to the codes in these links:

http://www.erlandsendata.no/english/index.php?d=envbadacexportado
http://www.erlandsendata.no/english/index.php?d=envbadacimportado

The code can be modified to execute queries that are built-in to the Access database and return results to any particular sheet in the current workbook at the click of a button...

Ranga

[rangudu_2008]

The codes i sent above is the another way to store data from an excel spread sheet to an Access database and vice versa...