Vba code to import excel files Results

How to write VBA code to import excel files into a table in Access with a click of a button? The excel file and access table has same column name.

I am having a problem trying to find VBA code to import an excel 2007 file into my access 2007 dao database. My problem is that with each excel record I need to create 2 different records which will be written into different tables in my database.

i have AN access database ,which in one part of it will analyse a big amount of data with excell sheet format
i am using this code to import the sheet to my database

	DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Calls", "d:My Documentsmyfile.xls", True, "details!b1:h1068"

when trying to perform this code , it gives an error message that object do'snt in corect format , then i go to that workbook , save it as excell 97-2000 , then performing the code again , it success .
the problem is the big number of workbooks , it is so difficult to open it one by one , save as , repaet it every time ,
i tried this code to perform automatic open and save as , but it fail

Sub OpenSpecific_xlFile()
    Dim oXL As Object
    Dim oExcel As Object
    Dim sFullPath As String
    Dim sPath As String
    Set oXL = CreateObject("Excel.Application")
    On Error Resume Next
    oXL.UserControl = True
    On Error GoTo 0
    On Error GoTo ErrHandle
     sFullPath = CurrentProject.Path & "myfile.xls"
     With oXL
        .Visible = True
        .workbooks.Open (sFullPath)
        .workbooks.SaveAs FileName:=(sFullPath), FileFormat:=56
    End With
    Set oXL = Nothing
    Exit Sub
    oXL.Visible = False
    MsgBox Err.Description
    GoTo ErrExit
End Sub

but it break at save as point , it said this proberty not allowed here
any idea ?

Dear Friends,

I need to import Excel file into access table using VBA code and Excel file DOES NOT have any column heading. Any idea how to do import data without changing the file.


*EDIT: see 3rd post for solution/solvation


Right now I am building a database with items, coming from local dealers.
We send them a locked excel sheet, which they feed with dealer information and item information. In the excel sheet, this is being separated (in the background) in 2 different sheets. I have my master sheet, which is being maintained by the dealer, and I have 2 slave sheets, which I import into Access.

In my first steps into VBA I learned to copy/paste code to import those files into 1 table. (See code below).
I am a virtual VBA n00b, so know nothing about it, yet.
I will have to, though, since I plan to set up this database in VBA.

	Sub Link_To_Excel()
'Macro Loops through the specified directory (strPath)
'and links ALL Excel files as linked tables in the Access Database.
Const strPath As String = "serverpathNew Files for upload" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
 'Loop through the folder & build file list
strFile = Dir(strPath & "*.xlsx")
While strFile  ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'appending to tables called DealerLists and DealerContacts
For intFile = 1 To UBound(strFileList)
DoCmd.TransferSpreadsheet acImport, , _
"DealerLists", strPath & strFileList(intFile), True, "parts!A1:E229"
DoCmd.TransferSpreadsheet acImport, , _
"DealerContacts", strPath & strFileList(intFile), True, "contact!A1:F2"
MsgBox UBound(strFileList) & " Files were Imported"
End Sub

Now I have 3 challenges:One is, to know up front which dealer (information) is being imported. In the DealerContacts table, I have a lot of dealers. And if I get a new sheet of an existing dealer, I want to know if it already exists in the list or not. I want to be able to refresh the information, if the dealer already exists.How do I read out a specific cell, to be able to decide whether I should update or append the information? And how do I update the record, if it already exists? The second is, to remove old information in the item table and append new information.Once I have the dealer number, recorded in the previous session, how can I use this to delete all related records in an item list? Kind of: build a query, related to this dealer number, and delete all records with this specific number? Last but not least: to make sure I do not import empty cells (dealers have 228 records to their availability, but will seldom use that), I have a counter in sheet 4. This sheet contains only title and a number; number of lines filled with data.Essentially this is the same problem as 1., because I need to feed the information of this specific cell to a variable, which I then use to set the TransferSpreadsheet Command with the right amount of lines: import "parts!A1:E" & NumberOfLines This is the code I was trying, but it doesn't seem to work (fails at first line):

'Read the number of lines in the file 
Dim xl As Excel.Application
Dim xlsht As Excel.worksheet
Dim xlWrkBk As Excel.Workbook
Dim xlCell As Double
Set xl = CreateObject("Excel.Application")
Set xlWrkBk = GetObject(strPath & strFileList(intFile))
Set xlsht = xlWrkBk.Worksheets("Counters")
Set xlCell = xlsht.cells(2, "A")
DoCmd.TransferSpreadsheet acImport, , _
"DealerLists", strPath & strFileList(intFile), True, "parts!A1:E" & NumberOfLines
Set xl = Nothing
Set xlWrkBk = Nothing
Set xlsht = Nothing
Set xlCell = Nothing 

Who can help me out in this?

Thanks in advance,

Hi all,

I have an issue with an Excel file that I'm generating through a VBA code in Access.

I generate a report of some data that is in Access 2007, on an Excel file and add a "processing view" button to do some operations.

To add that button, I add a module to the Excel file in this way:

'Add the view button
 appExcel.ActiveSheet.Buttons.Add(520, 110, 130, 25).Select
 appExcel.ActiveSheet.Shapes("Button 1").Select
 appExcel.Selection.Characters.Text = "Processing View"
 appExcel.VBE.ActiveVBProject.VBComponents.Import "C:BLAChange_Views_Script.bas"
 appExcel.ActiveSheet.Shapes("Button 1").Select
 appExcel.Selection.OnAction = "Change_Views"

And when the file is generated, everything works fine.

The file is also saved in this way:

	appExcel.ActiveWorkbook.SaveAs FileName:="C:BLAabc": FileExtStr = ".xlsb": FileFormatNum = 50

The problem is when I close that generated file, and then I open it again. When clicking the button I get the message:
Quote: Cannot run the macro 'Change_Views'. The macro may not be available in this workbook or all macros may be disabled And effectively, I check the code and the imported module is not there.

Can Somebody help me, telling me how to save the imported module as well? I've tried to change the FileFormatNum and the FileExtStr, but I keep having the same results.

Thanks in advance, I hope somebody is able to help me.


hi people, i have following code that i atatched to command36 button to
import excel file (HKG.xls) at "input" tab from a folder C:Country. this will be put in a new table called "importCtryTest7". a problem i have here is the import works but not fully. I had missing data and upon research, i encountered the type conversion error - import errors. these i believed are caused by calculated fields in the file and is resolved by using paste special as values to format these fields (after whcih i do not have import errors). The issue is i am planning to loop as many as 100 of these files and the calculated fields need to stay in the file as the files are also used for other purpose whcih uses the calculations in the fields.

is there a way to automate paste special as values in vba (so i dont have to manually open up 100 files every month to copy/paste special) so i dont get the import errors? i surfed the net for this as found some hints (http://www.ozgrid.com/forum/showthre...t=21846&page=1) but couldnt get the vba to work after inserting the paste special code given in the link.

i am bad in vba so appreciate if anyone can advise me on how to modify below code to automate paste special values (whcih i deduce is possible based on the discussion in the link)


Private Sub Command36_Click()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
Dim i As Integer

strTable = "ImportCtryTest7"

Dim strWorkbooks(1) As String

Dim strWorksheets(1) As String

strWorkbooks(1) = "HKG.xls"
strWorksheets(1) = "Input"
blnHasFieldNames = True

'strPath = "C:Country"

' Create loop to import from the 3 different Workbooks
For i = 1 To 1
strFile = Dir(strPath & strWorkbooks(i))
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTable, _
strPathFile, blnHasFieldNames, _
strWorksheets(i) & "$"
strFile = Dir()
Next i
End Sub


I have 4 excel files I run /w VBA to import. The issue is 2 of the files have their worksheet name change every day by the system I am exporting them from. My original macro was very simple...

	DoCmd.RunSavedImportExport "123"
DoCmd.RunSavedImportExport "456"
DoCmd.RunSavedImportExport "789"

So the problem would be the saved imports dont work for those 2 files since the worksheet name changed. So I was able to create a file in excel /w this macro...

	Sub Update()
    Const fPath As String = "C:AccessVolumes"
    Dim sh As Worksheet
    Dim sName As String
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    sName = Dir(fPath & "*.xls*")
    Do Until sName = ""
        If Not sName = "UpdateSheets.xlsx" Then
            With GetObject(fPath & sName)
                .Sheets(1).Name = "1"
                .Close True
            End With
        End If
        sName = Dir
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

So now my problem is it is in excel... I don't know if it is even possible to write that up in access to be honest. I tried patching that up by doing this....

	Dim xl As Object
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("C:AccessVolumesUpdateSheets.xlsm")
xl.Visible = True
xl.Run "UpdateSheets.xlsm!Update"
xl.ActiveWorkbook.Close (True)
Set xl = Nothing

But when I run this I get a runtime error 440 Automation Error /w
xl.Run "UpdateSheets.xlsm!Update". The macro runs... but once it is done is when it throws the automation error. If I run it myself from excel it works so I am stumped.... any easier way to work around the changing worksheet name issue?

Hello all,

I am currently working on writing a VB Macro that imports data from over 1000 Excel files into a single Access DB.

Right now, I'm using a DoCmd.TransferSpreadsheet Loop which works but due to how the Excel worksheets are formatted, it just isn't usable.

So what I need to do is the same type of function (can extract data from every .xls file in a specified file path) but ONLY pulls specific data from cells (ie, A8, A15, H29...etc). I need to pull the data from these cells into specific fields within Access so that: Field1 contains all data from all the A8 cells in all the worksheets, Field2 contains all data from all the A15 cells in all the worksheets...and so on.

The worksheet from each Excel file is not the first worksheet in the workbook so it has to be identified in the process.

If anyone can help I would be greatly appreciative.

I've included my current macro below. Please ignore all the From 1 To 1 nonsense since I am including that so that at a later date I may add additional worksheets or tables.


	Sub AutomatedDataPull()

Dim strPathFile As String, strFile As String, strPath As String
Dim blnHasFieldNames As Boolean
Dim intWorksheets As Integer

' Replace X with the number of worksheets to be imported
' from each EXCEL file

Dim strWorksheets(1 To 1) As String

' Replace X with the number of worksheets to be imported
' from each EXCEL file (this code assumes that each worksheet
' with the same name is being imported into a separate table
' for that specific worksheet name)

Dim strTables(1 To 1) As String

' Replace generic worksheet names with the real worksheet names;
' add / delete code lines so that there is one code line for
' each worksheet that is to be imported from each workbook file

strWorksheets(1) = "Test Program .108.100"

' Replace generic table names with the real table names

strTables(1) = "Tracking"

' Change this next line to True if the first row in EXCEL worksheet
' has field names

blnHasFieldNames = False

' Replace C:Documents with the real path to the folder that
' contains the EXCEL files

strPath = "C:Access Test Docs"

' Replace X with the number of worksheets to be imported
' from each EXCEL file

For intWorksheets = 1 To 1

      strFile = Dir(strPath & "*.xls")
      Do While Len(strFile) > 0
            strPathFile = strPath & strFile
            DoCmd.TransferSpreadsheet acImport, _
                  acSpreadsheetTypeExcel9, strTables(intWorksheets), _
                  strPathFile, blnHasFieldNames, _
                  strWorksheets(1) & "$"
            strFile = Dir()

Next intWorksheets

End Sub

I want to import excel or csv into access but the field properties (e.g. txt vs numeric) may change at times. I know how to import via a Macro, and the import tools Access has to offer but would like the flexibility to do via VBA. I'm sure there is an easy way of doing this but not sure where to look. The Macro function allows the conversion to code but doesn't seem to help.

Automate the transfer of multiple excel files to one Access table.

Hello. I'm new to this forum and whilst not a complete newbie to Access or excel or basic VBA programming i didn't have a clue how to do this but i did find some code when searching for answers through google
However, i want to do two things in addition to what the code below does. I want to transfer these files but my records start on a certain row in excel. To Be exact row 17. I also want to be able move the imported excel files into an exported folder and exporting

I hope someone can give me some advise on how to do this


Here is the code i found

Option Compare Database
Option Explicit

Public Function ImportExcelMultipleFiles()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has Field names
blnHasFieldNames = False

' Replace C:Documents with the real path to the folder that
' contains the EXCEL files
strPath = "C:UsersKevinGoogle DriveATOSATOS Reports"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "ATOSData"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()

End Function


I am trying to import excel table to ms access using VBA. Firstly, user will be prompted an open file dialog and then before it become table in access I need to ensure that user has select correct excel table.
The correct table can be seen from the first field's name.

I have managed to transfer the table to access but still confuse how to make my code be able to validate/check the correct table.

For example, I just want to import Excel table with first field's named "DateLACCI" and will reject the others.

Please help me..

(Access 2003 user -- BEGINNER)

I am new to access and vba so please bare with me.

I need vba code to import multiple csv excel files into a access table.

I am new to what I am doing and need a little guidance.

Any help would be appreciated.

thank you in advance for your help and time.

I new to creating VBA code. I have over 200 Excel files I want to import them in to an access database (1 table) appending the files... I want to be able to click on a button and import the files. Help please.

I'm trying to write an Access VBA module which imports data from several Excel files. I'm using the DoCmd.TransferSpreadsheet command like this:

	DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel5, _
            "Table Name", _
            Path & "Excel Data File.xls", _

However, the difficulty is that some of the Excel files have more than one tab containing data. How do I specify which tab I want imported into which table? The Excel source files don't contain any named ranges, as the data in them is dynamic and number of rows changes from day to day. Is there a way of using VBA to import all the data from one specific tab into an Access table?

Hello Everyone,

I want to import selected fields/columns from a text file with 1412 fields into an Access 2007 DB but Access has a limitation to read only the first 255 columns. Is there a way or work around on this limitation. Any VBA code? Also another option is to use Excel make some macros or vba codes to manipulate the file to come up with a text file that contains only the columns i need in my Access DB.

Thank you very much for any help extended.

I am trying to import excel files from a specified folder into an Access database on a daily basis to update the tables. I found some code online that does the trick, however, it imports the new data to the table but instead of overwriting the data in the table it adds the data to the table so with each import it adds another copy of all the data. Below is the code I am using, I have been searching online for a solution but haven't had any luck... All help is appreciated. Thank you!

Sub Import_Excel_Archive_All()
'Import Data from All Worksheets in All EXCEL Files in a single Folder into Separate Tables via TransferSpreadsheet (VBA)
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim intWorkbookCounter As Integer
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPath As String, strFile As String
Dim strPassword As String
' Establish an EXCEL application object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number 0 Then
Set objExcel = CreateObject("Excel.Application")
blnEXCEL = True
End If
On Error GoTo 0

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True
' Replace C:MyFolder with the actual path to the folder that holds the EXCEL files
strPath = "C:UsersScottDocumentsMy DropboxFundies DataBaseFundies Archives Daily Update"
' Replace passwordtext with the real password;
' if there is no password, replace it with vbNullString constant
' (e.g., strPassword = vbNullString)
strPassword = vbNullString
blnReadOnly = True ' open EXCEL file in read-only mode
strFile = Dir(strPath & "*.xlsx")
intWorkbookCounter = 0
Do While strFile ""
intWorkbookCounter = intWorkbookCounter + 1
Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath & strFile, , _
blnReadOnly, , strPassword)
For lngCount = 1 To objWorkbook.Worksheets.Count
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Next lngCount
' Close the EXCEL file without saving the file, and clean up the EXCEL objects
objWorkbook.Close False
Set objWorkbook = Nothing

' Import the data from each worksheet into a separate table
For lngCount = colWorksheets.Count To 1 Step -1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"" & colWorksheets(lngCount), _
strPath & strFile, blnHasFieldNames, _
colWorksheets(lngCount) & "$"

Next lngCount
' Delete the collection
Set colWorksheets = Nothing
' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPath & strFile
strFile = Dir()
If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing

End Sub

I'm using vba code to import an Excel worksheet (xlsx) to Access 2007. One of the Excel fields contains more than 255 characters, but only the first 255 characters are importing, even though the Access field is a Memo field. Any idea why, or how I can resolve?
Do While xlc1.Value ""
rst1.Fields("ProjNbr").Value = xlc1.Offset(0, 0).Value
rst1.Fields("ProjName").Value = xlc1.Offset(0, 1).Value
rst1.Fields("ProjDate").Value = xlc1.Offset(0, 3).Value
rst1.Fields("ProjAmt").Value = xlc1.Offset(0, 4).Value
rst1.Fields("Comments").Value = xlc1.Offset(0, 6).Value
Set xlc1 = xlc1.Offset(1, 0)

Please don't question why I'm using vba code or suggest other methods of importing an Excel file. I have my reasons. Just wondering how I can import all the characters from the Excel field into the "Comments" Memo field.

Thanks for your help.

I am creating a application to import a Excel worksheet and export a text file. I want the application to run when the user clicks a ICON on their desktop. I was trying to code a Macro using the RunCode action.
When I try to put in the function name I get the error "The expression you entered has a function name that Microsoft Office Access can't find". In the pull down it shows the miodules but not functions. It will not allow me to select the function or the module. Here is my code.

Module MarginRankImport

Private Function ImportMarginRanking()
FileName = GetFileName("Select the Margin File", "CMarginRankApp")
If FileName = "" Then GoTo Exit_ImportMarginRanking

FromPath = Left(FileName, InStrRev(FileName, ""))

MsgBox "Importing records - Please wait", vbExclamation

DoCmd.RunSavedImportExport "Import-Margin Rank Assignment"


DoCmd.SetWarnings False

DoCmd.RunSQL "delete * from [ExportMarginRank]"
DoCmd.OpenQuery "ExportMarginRank Query"

DoCmd.SetWarnings True

ExpSpec = "MarginRank Export Specification"
TableName = "ExportMarginRank"
FileName = "MarginRank.txt"
DoCmd.TransferText acExportFixed, ExpSpec, TableName, FromPath &


End Function

So I'm hacking and slashing my way to learn VBA and I've come across something that no clue to figure out. I have a Msgbox that prompts the user to import a file ("Yes") or opens the file to be edited ("No") and then (hopefully) upon saving and closing the Excel spreadsheet I'd then like it to be imported. Possible?

Be warned I'm sure this is some ugly code and tips/pointers are welcome.

	Public Function Import_Inventory()
    Dim Msg As String, Button As Variant, Title As String, Response As Variant, fDialog As FileDialog
    Dim strFileName As String, XL As Object
        Msg = "Have you removed any duplicates and blank spaces from the file you are importing?"
        Button = vbYesNo + vbDefaultButton2
        Title = "Import File"
    Response = MsgBox(Msg, Button, Title)
        If Response = vbNo Then
                Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
                Set XL = CreateObject("Excel.Application")
                    With fDialog
                        .InitialFileName = "N:sharedLoriBSLR"
                        .AllowMultiSelect = False
                        .Title = "Please select file to edit"
                        .Filters.Add "Excel", "*.xls"
                        .Filters.Add "All Files", "*.*"
                            If .Show = True Then
                                strFileName = .SelectedItems(1)
                                XL.Workbooks.Open strFileName
                                XL.Visible = True
                            End If
                    End With
                Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
                    With fDialog
                        .InitialFileName = "N:sharedLoriBSLR"
                        .AllowMultiSelect = False
                        .Title = "Please select file to import"
                        .Filters.Add "Excel", "*.xls"
                        .Filters.Add "All Files", "*.*"
                            If .Show = True Then
                                strFileName = .SelectedItems(1)
                                DoCmd.SetWarnings False
                                DoCmd.RunSQL "DELETE * FROM tblInventory"
                                DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblTemp", strFileName, True
                                DoCmd.RunSQL "INSERT INTO tblInventory SELECT * FROM tblTemp"
                                DoCmd.DeleteObject acTable, "tblTemp"
                                DoCmd.RunSQL "UPDATE tblInventory SET [Available]=0 WHERE [Available]

Not finding an answer? Try a Google search.