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."



Sponsored Links:



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