Importing Excel table into Access via button

[claytonjgordon]

I built a database on access that uses a SQL server for the back end tables. That part works great but the business group wants to add a new feature that allows them to append records onto one of the tables from Excel spreadsheets.

I'm thinking of doing this in two parts.
1) import the table into access
2) do an append query to add the information to the SQL table.

These users have no technical experiance so asking them to use the 'NEW 'import table'' feature and then run a query isn't practical.

I would like them to be able to push a 'browse' button and then select the excel sheet from their computer and then all the other steps would be automated via VBA.

However, I've been unable to identify a function that promts the 'browse' feature. The closest thing I have found is the TransferDatabase DoCmd which requires a spesific path and exact file name.

Is what I'm thinking possible? and if so, any idea where I could look to find information on how to do this?

Any help you could provide is appreciated.

Thanks

Clayton







Dominus Nihil
(Master of Nothing)

 

[PHV]

You may consider this:
DoCmd.RunCommand acCmdImport

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[claytonjgordon]

That successfully triggers the import tool; do you know of a way to automate the answers to the various steps after selecting the file?

I would want it to:
select the first tab in the spreedsheet
Select first column contains column headings
In an existing table (and provide table name)
and keep existing name.
(These are the steps in the wizard, prompted by the import function)

Then I could control what the name of that imported file is and thus preserve the next part of the code which would run the append query of that table onto the main table. If the user is allowed to name their import anything they want, the append query wont know what table to reference.






Dominus Nihil
(Master of Nothing)

 

[CautionMP]

claytonjgordon,
You can create your own 'workbook browser' from that walks the user through selecting the workbook and worksheet. Once they have done this you can feed the arguments to [tt]TransferSpreadsheet[/tt].

Assuming you have an new form that has:
[tt]TextBox : txtWorkbook
ListBox : lstWorkbooks
Command Button : cmdPickFile[/tt]
Set the Row Source Type on [tt]lstWorkbooks[/tt] to [tt]Value List[/tt] then set the On Click event for [tt]cmdPickFile[/tt] to [tt][Event Procedure][/tt] and add the following code:

On Error GoTo cmdPickFile_Click_Error
Dim strDefaultLocation As String
strDefaultLocation = Get_ExcelLocation(Left(strDefaultLocation, InStrRev(strDefaultLocation, "\")))
If Len(strDefaultLocation) <> 0 Then
  Me.txtWorkbook = strDefaultLocation
  Me.lstWorkbooks.RowSource = Get_WorksheetNames(strDefaultLocation)
End If

cmdPickFile_Click_Exit:
Exit Sub

cmdPickFile_Click_Error:
Select Case Err.Number
  Case 94 'Invalid Use of Null
    strDefaultLocation = "C:\"
    Resume Next
  Case Else
    Debug.Print Now, "cmdPickFile_Click", Err.Number, Err.Description
End Select

Then add the following supporting routines to a new or exisitng module:

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
  lStructSize As Long
  hwndOwner As Long
  hInstance As Long
  lpstrFilter As String
  lpstrCustomFilter As String
  nMaxCustFilter As Long
  nFilterIndex As Long
  lpstrFile As String
  nMaxFile As Long
  lpstrFileTitle As String
  nMaxFileTitle As Long
  lpstrInitialDir As String
  lpstrTitle As String
  flags As Long
  nFileOffset As Integer
  nFileExtension As Integer
  lpstrDefExt As String
  lCustData As Long
  lpfnHook As Long
  lpTemplateName As String
End Type

Public Function Get_ExcelLocation(Optional DefaultDirectory As String = "C:\") As String
On Error Resume Next
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String

sFilter = "Excel files (*.xls)" & Chr(0) & "*.xls"
With OpenFile
  .lStructSize = Len(OpenFile)
  .hwndOwner = Application.hWndAccessApp
  .lpstrFilter = sFilter
  .nFilterIndex = 1
  .lpstrFile = String(257, 0)
  .nMaxFile = Len(OpenFile.lpstrFile) - 1
  .lpstrFileTitle = OpenFile.lpstrFile
  .nMaxFileTitle = OpenFile.nMaxFile
  .lpstrInitialDir = DefaultDirectory
  .lpstrTitle = "Select the Expeditor workbook"
  .flags = 0
End With
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
  Err.Raise 8005, "Get_ExcelLocation", "No file was selected"
Else
  Get_ExcelLocation = Replace(OpenFile.lpstrFile, Chr(0), "")
End If
End Function

Public Function Get_WorksheetNames(FilePathName As String) As String
'Returns a delimited string you can use as the RowSource for an unbound ListBox set for ValueList
On Error GoTo Get_WorksheetNames_Error
Dim objWorkbook As Object, objWorksheet As Object
Set objWorkbook = GetObject(FilePathName)
For Each objWorksheet In objWorkbook.WOrksheets
  Get_WorksheetNames = objWorksheet.Name & ";"
Next objWorksheet
'Stop

Get_WorksheetNames_Exit:
Set objWorkbook = Nothing
Get_WorksheetNames = Left(Get_WorksheetNames, Len(Get_WorksheetNames) - 1)
Exit Function

Get_WorksheetNames_Error:
Debug.Print Now, "Get_WorksheetNames", Err.Number, Err.Description
GoTo Get_WorksheetNames_Exit
End Function

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[claytonjgordon]

Thank you very much for the information and code examples. It will take me awhile at my level to try to get this to work. So far I got the 'find Excel location' part to work. I'll let you know how it goes.



Dominus Nihil
(Master of Nothing)

 

[claytonjgordon]

The steps you put above are working great, but I can't seem to get the transfer spreadsheet to work.

Even if I don't use the variables and hardcode the data in the command directly, it doesn't do anything.

DoCmd.TransferSpreadsheet acImport, , "temp", "C:\Documents and Settings\cgordon\Desktop\test.xls", "Yes"

However, if I create a macro with the same information, it does work. Unfortunatly, the macro does not work with the field names.

The boxes from the macro that works is:

Import
Microsoft Excel 8-10
CCCCETBELLD
C:\Documents and Settings\cgordon\Desktop\test.xls
Yes
(Null/Blank)

Any idea on how to get either the macro or the DoCmd to work using the Me.txtWorkbook variable?


Dominus Nihil
(Master of Nothing)

 

[claytonjgordon]

I'm an idiot, I got it to work with

DoCmd.TransferSpreadsheet acImport, , "temp", Me!txtWorkbook, True

Thank you for your help!


Dominus Nihil
(Master of Nothing)