Append Excel pread sheet to Access table VBA

[Neilster]

Hi Guys

Can anyone point me in the right direction or know sufficient code to append an Excel spread sheet to an Access table VBA.

I want a command button to run it and add it to an existing table in my DB.

Something like Docmd.Importsreadsheet sort of thing.

Thanks for any help.

 

[James Deckert]

Is this a one-time import, or something to be done on a regular basis? If doing it repeatedly, and the Excel file keeps the same name, you could link to the Excel table (External Data>Import & Link>Excel). Then write an append query to append the data into your db table.
If it's a one-time import, just walk thru the Excel Import Wizard to create a table, then the append query to get the data into you db table. Then delete the Excel table

 

[The_Doc_Man]

Do this in a macro in several steps, or in VBA button-click code using a series of DoCmd actions and RunSQL actions as appropriate.

First, import the spreadsheet to a temporary table, which you should erase before using.

Second, write an INSERT INTO query that selects the fields of the temporary table to be inserted to the appropriate fields of the permanent table. If there are conditions to be met regarding what records get imported, these conditions would go in a WHERE clause that is part of the INSERT INTO query.

Finally, erase the temporary table.

Look up INSERT INTO syntax if you weren't sure, but it might look like

INSERT INTO tblKeeper (fldA, fldB, fldC) SELECT fld1, fld2, fld3 FROM tblTemporary ;

or

INSERT INTO tblKeeper (fldA, fldB, fldC) SELECT fld1, fld2, fld3 FROM tblTemporary WHERE fld1 > 0 AND NOT ISNULL( fld2 ) etc. ;

I'm shooting from the hip here so don't trust that I've got the syntax exactly right, but the great Google brain can easily help you. The concept is that a multi-step process can be programmed once behind a button and after that, it doesn't matter what is behind the button, just click it. Spend some time up-front getting the button right and then you don't care what happens behind the button.

 

[ChrisTheIntern]

Going on a limb here + also a lunch break right now. Here is some's code I found on a different forum. I tweeked it to fit my needs but I found it very helpful in getting mine started.

This pretty much covers most of the ways you can import from excel:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean [LEFT][SIZE=2]blnEXCEL = False

[COLOR=#008000]' Establish an EXCEL application object[/COLOR]
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

[COLOR=#008000]' Change [I]True[/I] to [I]False[/I] if you do not want the workbook to be
' visible when the code is running[/COLOR]
xlx.Visible = True

[COLOR=#008000]' Replace [I]C:\Filename.xls[/I] with the actual path and filename
' of the EXCEL file from which you will read the data[/COLOR]
Set xlw = xlx.Workbooks.Open("C:\Filename.xls", , True) [COLOR=#008000]' opens in read-only mode[/COLOR]

[COLOR=#008000]' Replace [I]WorksheetName[/I] with the actual name of the worksheet
' in the EXCEL file[/COLOR]
Set xls = xlw.Worksheets("WorksheetName")

[COLOR=#008000]' Replace [I]A1[/I] with the cell reference from which the first data value
' (non-header information) is to be read[/COLOR]
Set xlc = xls.Range("A1") [COLOR=#008000]' this is the first cell that contains data[/COLOR]

Set dbs = CurrentDb()

[COLOR=#008000]' Replace [I]QueryOrTableName[/I] with the real name of the table or query
' that is to receive the data from the worksheet[/COLOR]
Set rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset, dbAppendOnly)

[COLOR=#008000]' write data to the recordset[/COLOR]
Do While xlc.Value <> ""
      rst.AddNew
            For lngColumn = 0 To rst.Fields.Count - 1
                  rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
            Next lngColumn
      rst.Update
      Set xlc = xlc.Offset(1,0)
Loop

rst.Close
Set rst = Nothing

dbs.Close
Set dbs = Nothing

[COLOR=#008000]' Close the EXCEL file without saving the file, and clean up the EXCEL objects[/COLOR]
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing[/SIZE]

[/LEFT]

 

[Neilster]

Nice one guys!

I'll give these methods a try and get back to you with the outcome.

Again thanx for your help.

 

[Neilster]

I have tried this version and I get a

'compile error

variable not defined'

Because of this line - strFile = Dir(strPath & "*.xls")

Private Sub Command306_Click()

Dim blnHasFieldNames As Boolean
Dim strWorksheet As String, strTable As String
Dim strPath As String, strPathFile As String
' 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:\Users\owner\Desktop\FFTemplate.xls"
' Replace worksheetname with the real name of the worksheet that is to be
' imported from each file
strWorksheet = "FTTemplate"
' Import the data from each workbook file in the folder
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
strTable = "tbl_" & Left(strFile, InStrRev(strFile, ".xls") - 1)
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTable, strPathFile, _
blnHasFieldNames, strWorksheet & "$"
' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile
strFile = Dir()
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
' Close the EXCEL file without saving the file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing
End Sub

 

[ChrisTheIntern]

It looks like you are using the template for importing data from a specific worksheet in ALL EXCEL Files in a single folder into separate tables in Access. Just double checking that is what you want.

strPath should not have the Excel file name in it. The path should stop at the final subfolder that contains your Excel files. In your case that would be:

strPath = "C:\Users\owner\Desktop\"

strWorksheet should be the sheet name you want in the excel files. For example when you create a new Excel file, the default sheets are named "Sheet1", "Sheet2", and "Sheet3".

 

[Neilster]

Ok thanks for that I'll give it a try and let you guy's know the outcome.

 

[Neilster]

What I want to do is run VBA so that I can import an Excel spreadsheet that is a template with the same headings and append to an existing table in Access (just like the import Excel wizard as standard in Access)

I've tried this as well but not running due to compile errors.

Private Sub Command306_Click()

Dim filepath As String
Dim User As String

User = Environ("Username")
filepath = "C:\User\" & User & "\Desktop\Template.xlsx"


DoCmd.TransferSpreadsheet acImport, , "FromExcel", filepath, True

Else

MsgBox " File not found. Please check filename or file location."

End If

End Sub

:banghead:

 

[Gasman]

Would the compile error be 'No If found'?, or along those lines?

You have an Else statement and End If, yet no If statement.?

If you are that unfamiliar with VBA it might be better doing it with macros?

Access 2007 and above had the facility to save imported data macro.?

I used such a function to literally do what you are trying to do.

1. I imported the Excel files to a temp table Table1
2. Extracted required data from Table1 to my working table Table2.
3. Updated Table2 with required info.

The reason macroes were involved was so that others who knew nothing of VBA could amend the steps using simple macros and queries.

The filename had to remain the same for this to work each time.

HTH