Import Excel file to Access

  • Thread starter Thread starter dp724 via AccessMonster.com
  • Start date Start date
D

dp724 via AccessMonster.com

I'm using the method described by Ken Snell (link below) to import files in
to Access. I've been successful with getting file directory to open, but when
I select a specific file to import, the file that gets imported is the first
file in the directory. How do I edit the code below to just import the file I
select. I don't need to loop through the directory to import all files in the
folder, I only want to import the file selected, so I've removed the loop
statement

http://www.accessmonster.com/Uwe/Fo...-Excell-Macro#C29442DD5241yvonnemichelenoosfr


Code being used:

Private Sub ExcelDataImport_Click()
Dim strDirectory As String, strFilter As String
Dim strFileName As String, strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
strDirectory = Left(strInputFileName, _
InStrRev(strInputFileName, "\"))
strFileName = Dir(strDirectory & "*.xls")

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"BarChartData", strFileName, True, "AccessData!A1:P101", strFileName =
Dir()

strFileName = Dir()

End Sub


VBA is all new to me and none of editting I've tried out works.
Any help is greatly appreciated.

Dave
 
Ok, I got it working!

Dim strDirectory As String, strFilter As String
Dim strFileName As String, strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"BarChartData", strInputFileName, True, "AccessData!A1:P101"
 
Sorry about the erroneous info in my original post's code. Glad you got past
it and put the correct code together.
 
Back
Top