Need major HELP !!!!!!!!!!

[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

 

[Jim Carlock]

Couple things here...
1) Put a proper title on the messages you post here.
2) Declare your variables using proper Hungarian notation
that makes at least a little sense.
ie, XLFile should be declared as follows:
Dim strFileName As String
It is not an XL anything... it's a string. You are using it to
reference a particular filename.
3) Make sure you have the proper references configured.
ie, Inside the VBA editor click on Tools, References, make
sure Excel is referenced there.
4) Go to the folder where the Excel spreadsheet is, then right
click there inside of Explorer, and click on New, then you click
on Access Database.

You'll need to set up a table. All tables have field names. I'd
suggest keeping the names as simple as possible.
e.g.:
NameFirst
NameLast
NameMI
Addr1
Addr2
....and so on...

When you run into specific problems, feel free to post a
specific question. Continue posting to this thread. Folks
will pleasantly offer their help. And remember... the more
specific your question is the better off you'll be.

Hope that helps.

--
Jim Carlock
http://www.microcosmotalk.com/
Post replies to the newsgroup.


:
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