Listing Excel worksheet & fields in VB

[ssharrock]

I am trying to list all the worksheets in any Excel spreadsheet that I choose in VB. Once I select the worksheet I want to send the data on that page to an Access database. A solution to the first problem would be greatly appreciated.

[Cimperiali]

'Put a reference to microsoft excel 8.0 or 9.0 Object Library
private xlapp as Excel.Application
private xlWorkbooks as Excel.Workbooks
private xlWbook as Excel.Workbook

private Sub Command1_Click()
set xlapp = new Excel.Application
xlapp.Visible = true
set xlWorkbooks = xlapp.Workbooks
for Each xlWbook In xlWorkbooks
Debug.print xlWbook.Name 'your worksheet name
next
set xlWbook = nothing
xlWorkbooks.Close 'may be you do not need this line...
set xlWorkbooks = nothing
xlapp.Quit
set xlapp = nothing

End Sub





Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[ssharrock]

Ok .. Thanks for that .. Second part of question know becomes relevant .. If I have a spreadsheet with a sheet like the following,

Firstname Surname TelephoneNo
Shane Sharrock 555 1929 1922
Micky Mouse 152 2222 2111

with the first row being column names, how do I import the data into an access db. DAO/RDO/ADO or something else. Is there a site that deals with Word/Excel automation in VB? I can't find much of great detail. Thanks for the first bit of code.

[Cimperiali]

'first, I made a missing in previous code: the
'following should be better:
private xlapp as Excel.Application
private xlWorkbooks as Excel.Workbooks
private xlWbook as Excel.Workbook
private xlSheet as Excel.Worksheet
private Sub Command1_Click()
Dim intI as Integer
set xlapp = new Excel.Application
xlapp.Visible = true
set xlWorkbooks = xlapp.Workbooks
xlWorkbooks.Open "c:\myxls.xls" 'your excel sheet

for Each xlWbook In xlWorkbooks
for intI = 0 to xlWbook.Worksheets.Count - 1
set xlSheet = xlWbook.Worksheets(intI + 1)
Debug.print xlSheet.Name 'you will find more than one sheet here!
set xlSheet = nothing
next intI
next
set xlWbook = nothing
xlWorkbooks.Close
set xlWorkbooks = nothing
xlapp.Quit
set xlapp = nothing

End Sub
'second, you may connect to access table via ado,
'and add/update the value of a table with value retrieved form Excel, which
'is kind a quite piece of code and (unlukily) I haver not the time rhight
'now...
'to connect to access via ado
'add a reference to Microsoft Activex Data Objects 2.5 library
Dim cnn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim strConn as string

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydb1.mdb;Persist Security Info=false"
set cnn = new ADODB.Connection
cnn.ConnectionString = strConn
....




Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater