Imports Override

[filnigeria]

I have VBA code to import a whole excel book 14 sheets

the 14 sheets in excel are created by a template inturn imports from an EDI
file

MY PROBLEM
-----------------

When i import into access the temp file created by the excel template,it
doesn't add the new record in sted it overwrites the existing ones

here is the VBA code for the access inport procedure


'************************************
Private Sub SheetImporter()
Dim XLapp As Object
Dim XLFile As String
Dim XLSheet As String
Dim XLRange As String
Dim TableName As String
Dim z As Integer
Dim SheetCount As Integer


Set XLapp = CreateObject("Excel.Application")



XLapp.Visible = True 'Excel is visible!! or if False not visible!!
XLFile = "D:\Joss Blaze\temp\temp.xls" 'Your File
TableName = "" 'Table to import into
XLRange = "!a1:z3000" 'Specifies the area to be imported

Set XLwb = XLapp.Workbooks.Open(XLFile) 'Opens your file in Excel

'if you want to import all sheets in your Excel file into one table use the
following 6 lines of code

'if you need only sheet 3, remove the for-next construct, keep the 3 lines
of code within and change the code from .Sheets(z).Name
' to .Sheets(3).Name

SheetCount = XLapp.ActiveWorkbook.Sheets.Count 'Gives you the total number
of sheets
For z = 1 To SheetCount
XLSheet = XLapp.ActiveWorkbook.Sheets(z).Name 'get name of sheet number
z
XLSheet = XLSheet & XLRange 'add range to sheetname
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
XLapp.ActiveWorkbook.Sheets(z).Name, XLFile, False, XLSheet

Next z

MsgBox "Imported Successfully "

XLapp.Quit
Set XLapp = Nothing
Set XLwb = Nothing

End Sub

'************************************

one more thing how on earth do i run the code without having to open the
editor and executing it from there

Really need help

Jordan

 

[Larry Daugherty]

Hi,

In a command button's Click event put:

SheetImporter

So help me I've never used the TransferSpreadsheet functionality but here's
a suggestion based on the code you already have that almost works: Always
import into an Interim table that will have exactly the same fields as the
ultimate destination table with one notable exception: If you have an
autonumber field in the final table, don't put it in the Interim table.
Then, in the For ... loop you already have, run a delete query against the
Interim table (note, you may not need the delete query as you say the
current code is overwriting earlier transfers... If you don't need it,
don't do it), do the TransferSpreadsheet to the Interim table, then run an
Append query to append the contents of the Interim table to the Final table.

You're ready for the Next cycle

That will leave the Interim table setting with the contents of the last
spreadsheet transferred but, so what? It's relatively small.

HTH