Solved

vba to import excel file into a table

Posted on 2004-08-28
20
52,702 Views
Last Modified: 2011-08-18
i need to create a button that imports an excel file as a table, asks for the location of the file to import, checks if the colum "Discount" is not null (and if null puts a zero as a value) and says "everything ok" if really everything ran ok... thanks!!!
0
Comment
Question by:catalini
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 4
20 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 11921963
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
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
ID: 11921985
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 11922076

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

0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11922114
...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.
0
 

Author Comment

by:catalini
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! :-)
0
 

Author Comment

by:catalini
ID: 11925379
i tryed to past the code to open through api (shanesuebsahakarn), but it did not work...
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11925446
When you say it didn't work, what error message do you get?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 11926151


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

Is there any rule against this?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11926166
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 11926324
that is just his opinion
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11926361
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.
0
 

Author Comment

by:catalini
ID: 11932149
could you explain me better how to do it? what should i copy and paste? thanks.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11932278
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.
0
 

Author Comment

by:catalini
ID: 11932366
i receive an error on this line...

DoCmd.TransferSpreadsheet , acImport, "MyTable", strFile, False
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11932452
You have an extra , in there - remove the , before the acImport.
0
 

Author Comment

by:catalini
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?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11932760
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"
0
 

Author Comment

by:catalini
ID: 11941937
but why do the empty fields appear?

thanks
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11941962
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.
0
 

Author Comment

by:catalini
ID: 11942036
great!

thanks again for your very nice answers!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question