import excel files into access automatically

I’m very new to using VBA in Access, so if anyone can help me I would greatly appreciate it.

This is what I need help with:
I have to create a system that automatically imports Excel files from a folder in the hard drive into an Access table. I want to make it so all the users have to do is hit a button and everything happens automatically. I know it’s possible because I was able to create it using VBA in Excel but I can’t make it work for Access.

I believe I have to use "currentproject.path" instead of "ThisWorkbook.Path" but I’m not entirely sure. All the files are save in this format: “Seating Chart Report- city name.xls” The city name is different for every file. The subfolder all the files are saved in is called “Attachments” and the table they need to be imported into is called “Seating Chart.” The Excel files and Access database are both located in the same main folder called "Seating Chart Project."


Post your answer or comment

comments powered by Disqus
Hello,

I need to code the process of importing the excel file into the Access. The excel file name and location is known and therefore I should open a window so the user would choose the file to be improted himself.

Please help.
Thaks.

I tried import an excel file into access and I get an error message:

"An error occurred trying to import a file: EmployeeFileEnrollment.xls. The file was not imported.

What is wrong with my file?

I have recently upgraded to Access 2002, I also still have Access 97.

While in Access 2002 I tried to import an excel spreadsheet into access. It kept causing my database to shutdown. "Microsoft Access has encountered a problem and needs to close. We are sorry for the inconvenience"....When I tried to import the same excel spreadsheet into my Access 97, it worked just fine. In fact the way I worked around this was to then imort the file from the Access 97 database into my Access 2002 database.

I then sent a table from Access 2002 to excel, saved it and then tried to import that back into Access 2002 - same problem.

I've looked on the Microsoft web site - didn't find anything - so I think I must have something in Access not set up correctly. Any thoughts? Marie

Our IT dept just reloaded MS Office and now I can't import excel files into Access. When I click on the GET EXTERNAL DATA button and then click on IMPORT, I get the usual dialogue box listing the file types with the exception of excel files. I installed and re-installed ALL selections having to do with Access and Excel and Data Access files using the Value Pack CD and still no changes.

Does anyone have any idea what is missing?

Thanks

MS Access 97 / Windows 98

How do I import multiple Excel worksheets within an Excel file into Access 2003. I am building a master table in Access to archive Excel data stored that is stored in multiple worksheets for each Excel file. I have tried TransferSpreadsheet but it only imports the first worksheet. Whomever can help I would appreciate your help. TIA

I have an app which validates and imports excel files into Access with a module that checks the Tab names then sets certain columns to "General" format.

The first file works ok then the second call of the module gives Run-time error 91 - "Object variable or With block variable not set"

Module:
------------------
Option Compare Database
Option Explicit

Function fncXLTabCheck1(frmM As Form, strPath As String, strImpFile As String, strType As String)
Dim appExcel As Object
Dim Wks As Worksheet
Set appExcel = CreateObject("Excel.Application")
appExcel.Workbooks.Open strPath & strImpFile, UpdateLinks:=0
For Each Wks In appExcel.Worksheets
If Trim(UCase(Wks.Name)) = "MONTH" Then
Wks.Name = "xxMonth-" & Format(Now(), "yyyymmddhhnn")
End If
Next
For Each Wks In appExcel.Worksheets
If Trim(UCase(Wks.CodeName)) = "MONTH" Then
Wks.Name = "Month"
frmM!intSheet = frmM!intSheet + 1
If strType = "OCEAN" Then
Wks.Columns("M:N").Select
Selection.NumberFormat = "General"
ElseIf strType = "AIR" Then
Wks.Columns("L").Select
Selection.NumberFormat = "General"
Wks.Columns("N").Select
Selection.NumberFormat = "General"
End If
End If
Next
appExcel.ActiveWorkbook.Close SaveChanges:=True
' set appExcel = nothing 'makes no difference
End Function
------------------

Setting appExcel = nothing makes no difference to the problem

Possible problem is brainfade, but I would appreciate any help!
Thanks

Hi all, I'm trying to get the following done:
I have code which import Excel files into my database with the "DoCmd.TransferSpreadsheet". which works great.
But I'm now splitting the database and I want to have the Excel files which a user selects in his frontend database, imported in my backend database. I don't see the possibility in the TransferSpreadsheet as this is set to the CurrentDb.
My temporary solution is to import in the frontend and CopyObject to the Backend, but does anyone have a direct solution?

Thanks, Rob

Hi,

I'm relatively new to access and am trying to set-up an automatic way of importing the data within excel files, into an access table.

For example, the excel files are in the following format:
Name,Age,Address,Occupation

We have hundreds of these files generated each day, therefore, I'd like to place them into a folder (ie c:people) and an access table populate with the following info:
"Filename",Name,Address,"CurrentDate"

Is this possible using some basic code?

Thanks,
Simon

Hello everyone. I am a newbie in access. I would actually like to import like only certain rows and columns from an excel file into a table into access. As the excel data would be changed dynamically, I have decided to use a linked table instead. Is it possible for the linked table just to display the specific rows and columns i want?Hope someone can help me! Thanks

Hi All,

i need a support to import a non-standard excel file to access 2007.
The excel file has 3 field: one of these field is a set of numerical codes separated by semicolons ( so in each excel cell there may be two or more numerical code separated by semicolons). The DB Access that I need to create, must have a record for each numerical code, and each record must have the others two field replicated. How I can create this file into Access 2007???
Thanks very much

Situation:
My client gets as receipt Excel files with data in 2 columns, 1 headers and 1 detail. I wish to load the detail into 1 Access table. I created a macro to flip the data so that I have 2 rows. The user can easily run this macro and save it before importing the spreadsheet.

Problem:
The Import Spreadsheet Wizard does not like some of the column headings so I get the "The search key was not found in any record." when trying to import the data. I can't have the user changing the headers before importing these files each time. Is there a way to build an import procedure without the Wizard?

Problem2:
Still using the Import Spreadsheet Wizard I opt to not use the header in row 1. I successfully import 2 rows which I can work with. So I'm now trying to import a second file and want to append to the first file. The Import Spreadsheet Wizard is auto-selecting the first row to contain column headings. This does not work. Is there a way to build an import procedure to append without the Wizard?

Problem3:
So I created tblClaim to temporarily hold the 2 rows from the first Excel sheet. I saved the Import so the user can import new claims as they come in. I try to run the import task with the same file and I get "The specification failed to execute. Try re-creating the specification."
I deleted the table but I still get the same message. I can't think of a work-around for this one. The user would rather not manually enter this information.

Current Details:
Data entered into system saved as receipts in Excel sheets named LastNameDate
Excel Spreadsheet with all claims is maintained using most of data from receipt files.
Creating multi-purpose app that includes the all claims spreadsheet.
Need to load the receipt files into tblClaims.
Need to transfer tblClaims data into WorkSafeClaim table.

I am willing to manually build a macro to import this data. I just need a skeleton or the URL to some instructions.

Thanks

Hi,

I am trying to replicate the process of importing excel file into ms access as a table. Is there any way of doing it??

Currently, I am manually getting it done via External Data--> Import Excel Spreadsheet.

What I would like to achieve is to have a macro whereby a click of a button will get the spreadsheet into access as a table.

Thanks

Hi
Im trying to create a macro (or a button on a form) that will allow me to specify an excel file then import into database.

My goal is to not have to rename the excel files i am importing. its not an issue if the table is overriden each time

the "reverse" of the 'OutputTo' command is basically what i am looking for.

does anyone know how this can be achieved.

Thank you

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.

Thanks
Ria

Hi,
I'm trying to import an excel file to access using the command button with the following code:
DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="Test", filename:="C:/PKP/Test2.xls", HasFieldNames:=True

My excel file has many blank columns in the middle which i do not want to import. Does anyone know how I can show specific columns i want to bring into my access table?
thanks,

Is it possible to build a macro that will import a specific file in Access 2007 and then strip the un-necessary columns from that file? If so how would I go about doing it since Access does not have a recorder for the macro.

Thanks for your help.

All,

Is there a way to import an Excel workbook into Access keeping the various worksheets formatting such as Excel sheet's formulas, value, formats, comments, etc.? From google search so far, I can not find such a capability. I am able to import only the data from Excel workbooks into Access table, but loosing all the Excel workbook's formatting associated with the sheet.

Thank you.

David

Dear all,
Please advise on what is the best way to import Excel file into Access database. I have a Excel file that need to update everyday and import to access database(by using DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9) method. The problem i have is:
1) There is always empty records imported from the Excel file into the table.
2) Sometime Access will detected extra colume whereby is invisible in Excel file.

Please advise any good solution or any other method i can take in order to solve this import from excel problem?

Thanks in advance.

Can anybody help me in importing excel file "MasterData.xls" into my database "Deposit_Rec" and Table named "Master" thru VB script?

Thanks

So I have had Access for all of one day now. I am trying to import an excel file into access as a table. I successfully imported the first file but the second file gives me an error that says "An error occurred trying to import file ____. The file was not imported." What are the possible reasons this error has occurred? Both excel files I am importing are in the same format, so I am confused as to why one worked but the other did not. Thanks for any help.

Greetings from Guelph. First, my apologies if this has been covered already. I have searched through the forum and the Using Microsoft Access 2002 and 2003 books by QUE publishing but have not found an answer.
Each week I import an Excel file into Access 2002. Each week the file will have approx. 5,000 rows. The same fields will always be in the same order with consistent data formats, the same field names, field lengths etc.
Each week I want to normalize the imported table in Access to reduce space and make the database more efficient.
My question is, can I append the latest week's normalized table to that of the previous week's so that I end up with one master normaized table to run queries and reports against, rather than having a separate table for each week?
Any suggestions would be appreciated.
I have worked with and studied Access in books and online courses since 2002 but this is my first crack at setting up a database from scratch.
Thanks!

I have imported an excel file into access. After importing though, the text format changes the numbers to scientific notation, for example 723252238464 changes to 7.23252E+11. In excel the field was formatted to text, and is text as well for access. All of the fields with long numbers after importing have changed to scientific notation. How do I prevent that?

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.

Greetings,

Earlier this year, I imported a Google Earth KML file into Access for California's 515 groundwater basins. The basins are polygons and the KML line defining the polygon sometimes gets as large as 25,000 characters.

I was successful in importing the KML file by telling the wizard to import the lines into a memo field (which can contain up to ~ 65,000 characters). Everything worked well.

Now I have to re-import the KML file because of some spatial changes. But Access keeps truncating the memo field now! Is there someting I need to set in my preferences? My computer crashed since I did this last; maybe I need to reconfigure something.

Thanks for any help you can provide!

Jon Mulder
California Department of Water Resources
Red Bluff, CA


Not finding an answer? Try a Google search.