Problem with importing (overwrites insteads of add)

[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

 

[Dirk Goldgar]

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

For me, this code works -- once I add the necessary Dim statement for
XLwb -- and does append records to the tables if they already exist. It
does not replace existing records. Are you sure that's what's
happening, and that it isn't some other code, outside of this routine,
that is emptying or deleting those tables before this code runs? Or do
those tables maybe have unique indexes set such that the new records
can't be added due to key conflicts with existing records? I'm running
Access 2002; what version are you running?

As for running the code without opening the VB editor, you can put a
command button on a form, create an event procedure for its Click event,
and call the sub from that event procedure. That would be the simplest
of several possible ways.

 

[Dirk Goldgar]

Bon Jour Jordan,
I guess you are french or at least you speak french ;-)

Now back to business:
Unfortunately the transferspreadheet method is only able to overwrite
or create the given table but not append data to it.

I have not fouind this to be the case, at least not in Access 2002.

Probably you
should consider using temporary tables to import to and then append
the imported data to the final tables using Append Queries. If you
want to do that, change e.g. the
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
XLapp.ActiveWorkbook.Sheets(z).Name, XLFile, False, XLSheet

to
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
XLapp.ActiveWorkbook.Sheets(z).Name & "Temp", XLFile, False, XLSheet

If for some reason -- a different version, perhaps --
TransferSpreadsheet won't append for Jordan, this is a good suggestion.
Even better would be to *link* the spreadsheets, rather than importing
them, which could be done just be using acLink instead of acImport.
Then you'd just delete the linked tables when you're done appending from
them to your local table..

This should add to the name the word "Temp".
Then create Update Queries, 1 for each of your 14 tables -
unfortunately.
To run the Update Queries you can add the command
DoCmd.OpenQuery "YourQuery" at the end of the import script before
the 'End Sub'. "YourQuery" stands, of course, for the Update Query
names. You will have to add 1 DoCmd.OpenQuery for each Update Query.

You don't actually have to create 14 saved queries in the database. You
can use DoCmd.RunSQL or CurrentDb.Execute to execute SQL statements that
you create as character strings. For example,

With CurrentDb
.Execute "INSERT INTO YourTable SELECT * FROM [Sheet1Temp]"
.Execute "INSERT INTO YourTable SELECT * FROM [Sheet2Temp]"
.Execute "INSERT INTO YourTable SELECT * FROM [Sheet3Temp]"
.Execute "INSERT INTO YourTable SELECT * FROM [Sheet4Temp]"
.Execute "INSERT INTO YourTable SELECT * FROM [Sheet5Temp]"
.Execute "INSERT INTO YourTable SELECT * FROM [Sheet6Temp]"
.Execute "INSERT INTO YourTable SELECT * FROM [Sheet7Temp]"
.Execute "INSERT INTO YourTable SELECT * FROM [Sheet8Temp]"
.Execute "INSERT INTO YourTable SELECT * FROM [Sheet9Temp]"
.Execute "INSERT INTO YourTable SELECT * FROM [Sheet10Temp]"
.Execute "INSERT INTO YourTable SELECT * FROM [Sheet11Temp]"
.Execute "INSERT INTO YourTable SELECT * FROM [Sheet12Temp]"
.Execute "INSERT INTO YourTable SELECT * FROM [Sheet13Temp]"
.Execute "INSERT INTO YourTable SELECT * FROM [Sheet14Temp]"
End With

Even better would be to have the names of the tables in an array so you
could do something like this:

Dim astrTempName(14) As String
Dim i As Integer

' ... some code here puts the names of all the tables
' into astrTempName. Then ...

With CurrentDb
For i = 1 to 14
.Execute "INSERT INTO YourTable SELECT * FROM [" & _
astrTempName(i) & "]", dbFailOnError
Next i
End With

Last but not least, the second question : How to start the import.
I have to say that I made a mistake in one of my earlier postings -
You will have to change the first row from
Private Sub SheetImporter() to Function SheetImporter()
and at the end from
End Sub to End Function

The easiest way would be create a macro and add the following 2 lines
1. SetWarnings [In the Options at the bottom first line should be
'NO' - this will suppress any message from Access during the run of
the macro - you will still get the Message Box.]
2. RunCode [at the bottom type in : SheetImporter()]

Seems we differ on what is "easiest", but this is another good method.

 

[Guest]

;-))

Bernd