Import Excel Spreadsheet to Access

Hallo

How can I import Excel spreadsheet into a Access db Table in VB6 code? Example of the code would be appreciated.

Thanx

[PaulLewis]

I for one have not bothered with the Office 2000 versions as everything I maintain & support is using the Office 97 versions.

So using Excel97 and Access97:

  Dim myAccess As Access.Application
  Set myAccess = New Access.Application
  myAccess.OpenCurrentDatabase "c:\!dev\VB-World\db1.mdb"
  myAccess.DoCmd.TransferSpreadsheet acImport, 8, "Imported Book", "c:\!dev\VB-World\book1.xls", True
  myAccess.CloseCurrentDatabase
  Set myAccess = Nothing

Make the appropriate adjustments and try it out. You will have to add the referecne to the MS Access Object LIbrary and if you use Access 2000 it should be similar but as I said, I haven't got it...

Good luck.

Will this be possible in VB itself if you change Access.Application to something like VB.application. There reason that I ask is on MSDN it says that the TransferSpreadsheet only be used in Access itself...


Thanx for the help

[coox]

Herman, should be ok, coz what he's done is created a little invisible instance of Access. Anyway, try it and see...

[PaulLewis]

And I provided an example. If you want to try it for yourself then do so. All I can say is that the example was tested on my machine and it worked perfectly.

What you are not understanding properly is that as far as Access is concerned I AM running it. Creating an instance of Access.Application is the same as opening access. At that point, I have available to me anything that Access normally has.

This type of automation has been around since wayyyy back in the MS line of products. You can control anything from anything these days...

Regards

[coox]

Amen brother Lewis. Did you try it Herman?

Maybe I should make myself clearer..I tried the code and it is not what I need at this point. I need to be able to read the excel file and read the values per column and per record because the structure of the database table and the file is not the same. In other words the same way that you read a text file per line.

I am getting the following error when I run the code given... Automation Error. The server threw an exception!!

Thanx again for all the replies

[PaulLewis]

Usually the only time I need to parse an Excel file is when
I have an Access application to read it from. Also,
normally the excel file is more or less a table that easily
translates into an Access table.

Your original request I had misunderstood so now that you
are more clear, here is my advice:

Create some macros (actually VBA code) in Excel that
"reads" the file the way you want it to. You can simply
have the output go to debug.print or whatever.

Next, use VB to expose the Excel Application Object (as per
my example) and start using the code you created in the
Excel spreadsheet on the VB Object you have created.

If the main problem is that you do not know how to use VBA
from within Excel to manipulate the Excel sheet itself,
then this is where you should concentrate your learning.

Once you can use Excel's VBA proficiently, then using the
object model form within VB is no different to using it
from with Excel (with only minro mods needed)

The code below is a VB app which reads data from a simple
spreadsheet.

Option Explicit
Dim myExcel As Excel.Application

Private Sub Command1_Click()
  ReadAllData
End Sub

Private Sub Form_Load()
  Set myExcel = New Excel.Application
  With myExcel
    .Workbooks.Open FileName:="C:\!dev\VB-World\ExcelApplicationObject\Book1.xls"
  End With

End Sub
Private Sub Form_Unload(Cancel As Integer)
  myExcel.Workbooks.Close
  Set myExcel = Nothing
End Sub
Sub ReadHeader(myStart As String)
  With myExcel.ActiveSheet
    .Range(myStart).Select
    Debug.Print Selection.Text
  End With
End Sub
Sub ReadRange(myRange As String)
  Dim c As Excel.Range
  With myExcel.ActiveSheet
    .Range(myRange).Select
    For Each c In Selection
        Debug.Print c.Text
    Next
  End With
End Sub
Sub ReadAllData()
  ReadHeader ("A1")
  ReadRange ("A2:B3")
  ReadHeader ("A5")
  ReadRange ("A6:B7")
End Sub

The spreadsheet looks like this:

'  A        B
1  Header1	
2  a        1
3  b        2
4
5  Header2	
6  cat      TRUE
7  dog      FALSE

I know it's a little hard to read but it's the best I could
do...

Is this more helpful for you at all?

[Edited by PaulLewis on 10-10-2000 at 03:57 AM]

I would like to know something about this import method. I'm referring to :

Dim myAccess As Access.Application
Set myAccess = New Access.Application
myAccess.OpenCurrentDatabase "c:\!dev\VB-World\db1.mdb"
myAccess.DoCmd.TransferSpreadsheet acImport, 8, "Imported Book", "c:\!dev\VB-World\book1.xls", True
myAccess.CloseCurrentDatabase
Set myAccess = Nothing

Say for instance one of the columns in the Excel spreadsheet is the key in your table. I would like to capture the duplicate as well as null values it does not append in the table. Usually a Paste Error table is create, but not in this instance. I need these Paste Errors so that the user has the chance to change it manually....requirement.

Can anyone give help!?

Thanx

Hallo

Thanx for the code. Please tell me if I'm mistaken, but you will need Access on the PC where this piece of code should run?! Why I'm asking is that the code will run on thin-clients where Access is not loaded. I heard that there are certain runtime files (dll's ect.) that can be registered so that you do not need the Access package, but still invoke an instance (application) with the code above. Can someone either tell me what files do I need or some other way to work around this Access problem.

Thanx for the help...I need it desperately.