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.

 

[BerHav]

;-))

Bernd

 

[filnigeria]

Thank you very very much
just got up and here is this wonderful "solutions to all my problems"
im kinda new to VBA in access and thank God for wonderful people like you to
fish me out of my muck
i will try all wat you have said(of course) and will most likely be back to
ask more lol

from
Jordan

 

[filnigeria]

@ Dirk Goldgar
thank you for you help and support

but how can i call the sub with the button
if it isn't to much can you write an example of the code behind the button
(it will help me alot)

thank you very much

Jordan

 

[filnigeria]

ok its working
ut...
there is always a but with me

can i stop it from duplicating files that have already been imported

ohh and i dont know how to trasfer from one table to another and then delete
the temp tables

 

[Dirk Goldgar]

@ Dirk Goldgar
thank you for you help and support

but how can i call the sub with the button
if it isn't to much can you write an example of the code behind the
button (it will help me alot)

thank you very much

Jordan

The button would have an event procedure for its Click event, which
would look like this:

Private Sub cmdImport_Click()

Call SheetImporter

End Sub

That's all you'd need. "cmdImport", in the above, would be the name of
the command button.

 

[Dirk Goldgar]

ok its working
ut...
there is always a but with me

can i stop it from duplicating files that have already been imported

Only if there's some way to know whether the files have already been
imported. If you were doing it manually, how would you know? If you
can say that, then probably it can be programmed.

ohh and i dont know how to trasfer from one table to another and then
delete the temp tables

Copying data from one table to another is most easily done by executing
an append query. The very simplest case is if both tables have exactly
the same fields, in the same order. Then you can say something like
this:

CurrentDb.Execute _
"INSERT INTO TargetTable SELECT * FROM SourceTable", _
dbFailOnError

For an example more specific to your problem, see the example I posted
to BerHav earlier in this thread.

Deleting the temporary linked tables is as simple as

DoCmd.SetWarnings False ' disable warnings temporarily
DoCmd.DeleteObject acTable, "Sheet1Temp"
DoCmd.DeleteObject acTable, "Sheet2Temp"
' ...
DoCmd.DeleteObject acTable, "Sheet14Temp"
DoCmd.SetWarnings True ' enable warnings again

Or, if you have the table names in an array,

DoCmd.SetWarnings False ' disable warnings temporarily
For i = 1 to 14
DoCmd.DeleteObject acTable, astrTempName(i)
Next i
DoCmd.SetWarnings True ' enable warnings again

Or you could do the same thing using the methods of the
CurrentDb.TableDefs collection.

I must offer one warning about turning warnings off, as I was doing
above. If you're going to do it, you had better establish
error-handling in your procedure so that there is no way an error could
cause your procedure to exit without setting warnings back on again.
Otherwise, sooner or later the warnings will be left turned off and you
or your users won't get the warning and second chance you expect when
you're doing some critical operation.