vba to import excel file into a table

you can use this codes to import the excel file
you need to add Microsoft Office xx Object Library to your refernces

Tools>References select the Microsoft Office xx Object Library xx is the version number

Private Sub cmdGetExcelFile_Click()
Dim strFileName As String, strFileName1 as String, sTableName As String, strPath As String
Dim i As Integer
Dim fs As Object
Set fs = Application.FileSearch
With fs
.LookIn = "C:\ExcelFiles"
.fileName = "*.xls"
If .Execute(SortBy:=msoSortbyFileName, _
SortOrder:=msoSortOrderDescending) > 0 Then
For i = 1 To .FoundFiles.Count

strPath = .FoundFiles(i)
strFileName = Dir(strPath)
strFileName1 = Left$([strFileName], InStr(1, [strFileName], ".") - 1)
sTableName = Mid(Replace(strFileName1, " ", ""), InStr(1, (Replace(strFileName1, " ", "")), "-") + 1)

MsgBox "There were " & .FoundFiles.Count & _
" file(s) found. And you want to Import " & strFileName

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"" & sTableName & "", strPath, True
Next i
Else
MsgBox "There were no files found."
End If
End With

End Sub

08.28.2004 at 10:18AM PDT, ID: 11921985

Rank: Genius

Use the code here to bring up a file dialog:
http://www.mvps.org/access/api/api0001.htm

Paste it into a new module, and then you can get a file like so, let's say in a command button:

Private Sub cmdImport_Click()
Dim strFile As String
Dim strFilter As String

On Error Goto cmdImport_Error

strFilter=ahtAddFilterItem(strFilter,"Excel Files (*.xls)","*.xls")
strFile=ahtCommonFileOpenSave(Filter:=strFilter,OpenFile:=True,DialogTitle:="Select import file")
If strFile="" Then Exit Sub
DoCmd.TransferSpreadsheet acImport,,"MyTable",strFile,False
MsgBox "Everything ok!"
Exit Sub

cmdImport_Error:
MsgBox "Something went wrong! Error was: " & Err.Number & " " & Err.Description
End Sub

This line does the import:
DoCmd.TransferSpreadsheet acImport,,"MyTable",strFile,False

The last argument determines if the spreadsheet has field names (true) or not (false). You can specify the spreadsheet type in the second argument if you need to.

Accepted Solution

08.28.2004 at 10:48AM PDT, ID: 11922076

Rank: Genius

another version
you need to add Microsoft Office xx Object Library to your references
copy this to the form vba window

Option Compare Database
Option Explicit
Public fileName As String

Private Sub cmdGetFile_Click()
getFileName

Dim strFileName As String, sTableName As String
strFileName = Dir(fileName)
sTableName = Left$([strFileName], InStr(1, [strFileName], ".") - 1)
If fileName <> "" Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"" & sTableName & "", strFileName, True
Else
Exit Sub
End If

End Sub

Function getFileName()
' Displays the Office File Open dialog to choose a file name
.
Dim result As Integer
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select Files"
.Filters.Add "All Files", "*.*"
.Filters.Add "Excel Files", "*.xls"
.AllowMultiSelect = False
.InitialFileName = CurrentProject.path
result = .Show
If (result <> 0) Then
fileName = Trim(.SelectedItems.Item(1))

End If
End With
End Function

08.28.2004 at 10:57AM PDT, ID: 11922114

Rank: Genius

...why in the world would you declare the filename variable at module level?

One point to the original questioner. I neglected to read the second part of your question, about the Discount column. You'll need to run a query to update the Discount field after import - just put this line (modified for your table/field names) before the MsgBox line:
CurrentDb.Execute "UPDATE [MyTable] SET [Discount]=0 WHERE [Discount] Is Null"

If you need to do a lot of verification on your spreadsheet, I would import it into a table where all the fields are text and then append from that table to your real table. Importing from spreadsheets can be a little problematic at times because of how Access treats fields that contain only numeric characters (such as a phonenumber) - it will interpret these as numbers, not text.

08.29.2004 at 05:12AM PDT, ID: 11925277

is it possible to force access to adapt the excel file to the structure of the table already existing? thanks!
it should choose the first sheet of the excel file and do not take the empty lines... is it too difficult?

thanks again! :-)

08.29.2004 at 05:40AM PDT, ID: 11925379

i tryed to past the code to open through api (shanesuebsahakarn), but it did not work...

08.29.2004 at 05:55AM PDT, ID: 11925446

Rank: Genius

When you say it didn't work, what error message do you get?

08.29.2004 at 09:21AM PDT, ID: 11926151

Rank: Genius

<...why in the world would you declare the filename variable at module level?>

Is there any rule against this?

08.29.2004 at 09:25AM PDT, ID: 11926166

Rank: Genius

http://c2.com/cgi/wiki?GlobalVariablesAreBad

08.29.2004 at 10:00AM PDT, ID: 11926324

Rank: Genius

that is just his opinion

08.29.2004 at 10:08AM PDT, ID: 11926361

Rank: Genius

No, it isn't. It is considered bad programming style to use global variables for all of the reasons mentioned in that article. Do a google search, or post a question in some of the programming TAs, and you'll see what I mean.

There *ARE* uses for global variables, but if you don't have to use them, don't. There is no point in using a global variable just for the sake of it.

08.30.2004 at 08:37AM PDT, ID: 11932149

could you explain me better how to do it? what should i copy and paste? thanks.

08.30.2004 at 08:48AM PDT, ID: 11932278

Rank: Genius

Paste the code in the link that I gave you into a completely new module.

Then, you can paste the code that I posted into the OnClick event of your command button (ie this code):

Dim strFile As String
Dim strFilter As String

On Error Goto cmdImport_Error

strFilter=ahtAddFilterItem(strFilter,"Excel Files (*.xls)","*.xls")
strFile=ahtCommonFileOpenSave(Filter:=strFilter,OpenFile:=True,DialogTitle:="Select import file")
If strFile="" Then Exit Sub
DoCmd.TransferSpreadsheet acImport,,"MyTable",strFile,False
MsgBox "Everything ok!"
Exit Sub

cmdImport_Error:
MsgBox "Something went wrong! Error was: " & Err.Number & " " & Err.Description

You have to modify it slightly, but just change "MyTable" to the name of your own table.

08.30.2004 at 08:55AM PDT, ID: 11932366

i receive an error on this line...

DoCmd.TransferSpreadsheet , acImport, "MyTable", strFile, False

08.30.2004 at 09:02AM PDT, ID: 11932452

Rank: Genius

You have an extra , in there - remove the , before the acImport.

08.30.2004 at 09:25AM PDT, ID: 11932722

now it works... but after the import i receive a lot of empty record before my real records...

how can i specify which excel sheet to use? and the range of colums?

08.30.2004 at 09:29AM PDT, ID: 11932760

Rank: Genius

After the False option, you can specify the range of cells to import:
DoCmd.TransferSpreadsheet , acImport, "MyTable", strFile, "A1:D58"

for example. You can, however, just delete all the empty records after the import instead, for example:
CurrentDb.Execute "DELETE * FROM MyTable WHERE MyField Is Null"

08.31.2004 at 06:39AM PDT, ID: 11941937

but why do the empty fields appear?

thanks

08.31.2004 at 06:42AM PDT, ID: 11941962

Rank: Genius

This will be to do with the way your Excel file is formatted I imagine. Access often interprets empty cells as blank records if they appear above the real data, or if they once contained data that was deleted.

08.31.2004 at 06:48AM PDT, ID: 11942036