Access Import from Excel Overwrite


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
Err.Clear
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()
Loop
If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing

End Sub


Sponsored Links:



I am importing 1,500 records into my access program from excel and I recieved the following error

An error occurred trying to import file 'C:...09.xls'. The file was not imported.

Thank you in advance for all of your help!

Joe




I am coming up with errors when I'm importing from Excel
to Access. The error message is "field truncation." The
way I saved my Excel Sheet is in Tab Delimited so that
all my data comes through, otherwise, most of my data is
eliminated. What do I need to do so that all my data
comes through and I receive no more errors? All
suggestions appreciated.




I've created various tables in access,to store some data that will be added later with forms or imported from excel files.
the excel file I'm working with have several sheets, and repeated columns between themselves, so i created more one or two tables in order to store the data in only one place.
the problem is that i can't find the way to import "automatically" data from that sheets specifically in to the columns i want.
I'm gonna give an example to be easier: the sheets in excel have for example an entity column and other columns with entity data such as addresses, phone, that are common for several sheets of that file so i created an entity table in access DB in order to reduce the redundancy of the data in the DB and thus its size, but i don't know how to import just that columns to the entity table in access DB... Can someone help me?




I can take information from an Excel worksheet and import it into Access by specifying a path to an existing table and using DBS.AddNew to add a new record. I update the table DBS.Update and save the Primary key value to a variable which I return to the worksheet and add as a record number. intRecNum

I want to update the Access table periodically. I would like to go back to the Access Table from Excel ues the intRecNum value to pull up the specific record. I have looked for the info on Access Objects & Properties, but haven't found anything that works.

I think I need to replace DBS.AddNew something to point to an existing Record.... like DBS.ModifyRecord ?

I was thinking it would be something like :
DBS.Open "tblTest", ADOC, adOpenKeyset,
...
and then FindRecord.intRecNum (intRecNum being the Primary Key value I returned to Excel when I created the record.

I would like to know what command I would use to replace the DBS.AddNew and I would like to know the Object.Property I would use to access a specific Record in my table.

With my humble thanks,




We are setting up automatic importing of data from excel sheets to access database. I want to code something in access that at the click of a button will find the correct spreadsheet (excel sheets will be numbered corresponding to the current Access record), will append data from specific cells in the excel sheet to specific columns in an access table.
How do you import from excel to access specifying by cell etc?
thanks




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.




Hi

Is it possible to execute a Macro on an Access Database from Excel 2007?

If so, could someone advise me on how to do this?

Thanks in advance for your help.

Vik

ps. I don't know any VB Script, so please dumb down your response, if possible!




How would I open an Access form from Excel...
using vba? This opens up an Access window but dosen't open the form:


Private Sub openaccessform_Click()

Dim ac As Object
Dim str As String
On Error Resume Next
Set ac = GetObject(, "Access.Application")
If ac Is Nothing Then
Set ac = GetObject("", "Access.Application")
'ac.OpenCurrentDatabase "C:UsersCharlesDesktopPDOXDB.accdb"
'str = ac.CurrentDb.Properties("Shop Floor Data Entry")
ac.DoCmd.OpenForm "FORMVIEW"
ac.UserControl = True
Set ac = Nothing
End If
AppActivate "Microsoft Access"
ac.OpenCurrentDatabase "C:UsersCharlesDesktopPDOXDB.accdb"
'str = ac.CurrentDb.Properties("Shop Floor Data Entry")
ac.DoCmd.OpenForm "FORMVIEW"
End Sub

The name of the Access table is PDOXDB' the original name of the Access form I want to open from Excel is FORMVIEW
I renamed FORMVIEW in Access to AutoExec. This opens the form immediately when you click the table in Access.

I just want to open the Access form from Excel code and hide the table view some way

Thx for anyone's help.

cr




Hi All,

Im trying to run an access query from excel which takes parameter inputs.

I'm using the code from this link modified for my DB
http://www.ozgrid.com/forum/showthread.php?t=76848

My problem is the query pulls data from an SQL server connection which prompts for User ID and password the first time its run. When I run the code as is, it gives an error 'ODBC Connection to RegionalDB failed'
I'm suspecting its not getting passed the login prompt.
How do I send the UserID and password automatically?




Can anyone tell me how I can run an Access sub from Excel VBA?

The sub is:

	Code:
	Sub CloseDB()
DoCmd.Quit
End Sub

Thanks in advance




Hi,

Can someone advise how to import from excel. include the heading.

thanks




hello, i used an import from excel to create a new table. every week my excel spreadsheet gets updated with monthly totals for over 8000 accounts. i try and do an append import but its not bringing in the newest information. im new at this so maybe im doing it wrong??? any idea would be nice

Wolfie




I wrote some code so the user could push a button and import from excel, the problem is that my unique ID for my data is an autonumber and the data imported is not getting assigned one. How can I fix that?


	Code:
	
DoCmd.TransferSpreadsheet transfertype:=acImport, TableName:=acTable, FileName:=Me.Text1, HasFieldNames:=True





Hi there,

I'm trying to create a button on a form that will allow my database user to import from excel by browsing. I'm currently using the code below which allows them to import providing the file is in the right place with the right name

Dim stMessageBox As String

stMessageBox = MsgBox("Do you wish to update the records with a new spreadsheet", vbYesNo, "Import?")

If stMessageBox = vbYes Then
DoCmd.Save

Me.LastUpdated = Now
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "AUTUMN 06", "P:foldernamefilename.xls", True, ""
Exit Sub
End If

If stMessageBox = vbNo Then
Exit Sub
End If

Does anyone know of a way I can do the same but open a browser window and choose the Excel file? Any help is appreciated.

Cheers

Lee




Hi !

How I can remove empty rows when I import from excel ?

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"merkinnät", "thya.XLS", True, ""



Br

Timo




I'm running into problems with trying to create a macro and I was needing some advise... I think it would be better if it was done in VBA, but I'm not familiar with VBA... any help would be appreciated!

I'm trying to create a macro that will do the following...

1. Prompt the user to browse for an excel file.
I could use the "Run Command" -> "Import", but I'm trying to automate this process to keep the user interaction down.

2. Import the excel file (2 worksheets) into 2 table in access. The worksheets first row is a header row.
This is easy to do using the "Import" command to bring up the Import Wizard, but since I want to stay away from the wizard, I believe that I should use the "Transfer Spreadsheet" command". The problem I run into here, is that I'm required to put the filename and path into the "File Name" property. I am not sure how to set this up since I want to user to browse for the file.

Should I use VBA or can i do this with macros? Are there better commands to use or a better method to do this?
If i have to use VBA, can anyone help me or give me some further advice...

Thanks for any help you can offer.




Hi,

I have many files which also contains different sheets to import from Excel into Access database. I have written a macro below to import the file into Access.


	Code:
	Private Sub Import_Click()

Dim filenames As String
Dim sheet_names As String

filenames = Combo4.Value
sheet_names = Combo6.Value

    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acImport, 8, "filenames", "H:Project3filenames.xls", False, "sheet_names!A10:H100"
    ' delete table data
    DoCmd.OpenQuery "filenames Query", acNormal, acEdit
    ' import data
    DoCmd.TransferSpreadsheet acImport, 8, "filenames", "H:Project3filenames.xls", False, "sheet_names!A10:H100"
    ' show data
    DoCmd.OpenTable "filenames", acNormal, acReadOnly


End Sub


With reference to the code,
The combo4 (which is the different file names) refers to the drop down menu of a form in Access.
The combo6 (which is the different sheet names) refers to the drop down menu of the same form in Access.
From the drop down menus, I can choose filenames like fruits and sheet names like apple, banana. So when I click the import button, it will import the particular file that is chosen and show the table.

The problem is the directory (H:Project3filenames.xls) and the sheet names cannot be changed. For instance if I choose fruits from the drop down menu, then the directory shd be (H:Project3fruits.xls) and if I choose apple as the sheet name, it shd be “apple!A10:H100”.

How can I have 2 user-input values (from the drop down menus which contains 2 lists of filenames and sheet names) so that whenever I click the import button, it will import the file that the user has chosen from Excel into Access?




We have people in the field collecting data about buildings into multi-tab spreadsheets. (between 10 and 20 tabs total). We then want to copy this data into Access. I might try to do this a bit differently if given a choice, but I have no option but to have data collected in Excel and for Access to the the ultimate repository for it all.

Thus far I have put the worksheets into a layout that Access can recognize and created the tables in Access that will match the worksheets in Excel. The data is reasonably well normalized among the Excel Worksheets. (This is a too long story that i will wpare you reading).

My plan is to use append queries to copy the data from the worksheets into the Access tables. To do this, I believe I need to either import the worksheets into Access as tables or to link the worksheets.

With the option comes the quandary. There seems to be goods and bads of both options and I would love to hear from people who have done both or either of these methods. I have used both in the past for more limited scope tasks.

At first I liked linking. Once I have all of the Excel tabs linked, I can run the append queries. Then replace that spreadsheet with the next one, update the links and run the queries again. But then come the details.
1. I don't want people to have to manually update over a dozen links with every new spreadsheet coming in from the field.
2. Using the Import Data process in Access 2007, when setting up links, does not allow you to save the process to re-run it if you want to. Thus apparently requiring updating existing links. (That is, if I run the first spreadsheet with links, run my append queries, and then want to go to the next workbook, I can rename the first workbook (to break the links), then give my second workbook the same file name as when I set up the links and update the links so they now show the data from the second workbook. Then run the append queries again to get this second set of data into Access. And continue with however many workbooks we receive. The total number of these will be in the dozens.
3. So my problems with linking seem to involve the repeated renaming of spreadsheets and updating of many links to update the data in the linked tables in Access.

So I looked at importing worksheets as tables. Importing, not linking. Again, details.
1. In this case I can set a name for each import and presumably set up a macro that would run each of the imports, one after another. Is this true? I have not used macros in Access. Will they run these imports?
2. It would seem that after I run the append queries, I can set up a macro, or series of macros that will then delete out these imported tables, setting the stage to import them again from the next workbook to be processed.
3. So my problems here are whether I can use a macro to automate these processes (importing and later deleting the tables that are imported from Excel) and, again, the repeated naming and renaming of workbooks before each new running of the process.

And I just bet that there are issues that will arise that I have not thought about. I would surely appreciate all helpful comments about the two processes envisioned above and any other issues I may be missing.

Thank you in advance.
Robert




Hi,

I am new to using Access 2007 and i would appreciate help regarding importing data from excel to Access.

My Excel spreadsheet's format is as follows:

Name Group NO SSN Name 1 1 SSN Number Name 2 1 SSN Number Name 3 1 SSN Number Name 4 2 SSN Number Name 5 2 SSN Number Name 6 3 SSN Number Name 7 4 SSN Number and so on...


My Access table format is as follows (how i would like my information to appear on Access):

Group NO (Primary Key Autonumber) 1-Name 1-SSN 2-Name 2-SSN 3-Name 3-SSN 1 Name1 (SSN Number) Name 2 (SSN Number) Name 3 (SSN Number) 2 Name4 (SSN Number) Name 5 (SSN Number) 3 Name6 (SSN Number) 4 Name7 (SSN Number)
How can i import the information So that the group number from the excel sheet corresponds to the record number on the Access Table. Also, listing out the Name and their SSN to the corresponding columns for each group number.
Any help would be greatly appreciated and thank you for lending me your knowledge.