Getting data from excel sheet to Vb6

[MaxPunk]

Hi,

I am developing a payroll application in Visual Basic 6 and I need to get data
from an Excel worksheet. I am using DAO code to connect to the excel file. My
code is somewhat like this:

' declarations
'**************

Dim db1 As Database
Dim rs as recordset
Dim filepath as string 'for getting file path (I am using a Common Dialog)
Dim sheetname, sName As String ' Two variables: one for getting sheet name and one for Column value


' setup the Common Dialog box (CdlgImport)

CdlgImport.DialogTitle = "Select an Excel Worksheet!"
CdlgImport.Flags = &H8 Or &H1000
CdlgImport.DefaultExt = "xls"
CdlgImport.InitDir = App.Path & "\..\Imports" 'Import is the dir where xls file is saved
CdlgImport.Filter = "Excel Files (*.vls, *.xls)|*.xls;*.xls|All files (*.*)|*.*"
CdlgImport.ShowOpen


' Get Filename
filepath = CdlgImport.FileName
' if no file selected then exit sub
If Len(Trim(filepath)) = 0 Then Exit Sub
' Get Sheet name
sheetname = "Sheet1$" '<-----This is the place where my first problem pops up
'if the sheet names dont have a space in them then this
'code works fine eg: if the name is "GeneralInfo" the exact
'value gets saved in the sheetname variable. But in my case
'the sheet name is like "General Info" (contains spaces) so
'when I try to get the value in sheetname variable an error
'pops up telling that sheetname can not be found! Probably
'I am making a syntax mistake. Please tell me how to tactify it


'Code Continues

'Setting database variable to point to Excel file
Set db1 = OpenDatabase(filepath, False, False, "Excel 8.0;HDR=yes;")

' Prepare to get a recordset
Set rs = db1.OpenRecordset(sheetname)
If rs.RecordCount = 0 Then Exit Sub

' otherwise move to first

rs.movelast
rs.MoveFirst


'Following lines of the code are where my second error creeps in. The sheet form which i want
'to inport data is arranged in a manner that the very first row is not the place where the
'column names appear. instedd they appear from down to second row (take a look at he screen shot
'attached with this mail) So when I try to use the following code to read the column values an
'error pops in telling that the item is not found in the collection. How can I fix this?


'Code continues...

Do while Not rs.EOF

'Get the values form the Name Column and fill it in a List Box

List1.Clear
Do While Not rs.EOF

List1.AddItem rs!Name '(I get error here telling that item not found in collection)
rs.MoveNext

Loop



'Code Ends here....!

[westconn1]

you should declare your variables slightly differently, as you had it sheetname was a variant
Dim sheetname As String, sName As String

try

Set rs = db1.OpenRecordset(""""sheetname"""")  ' enclose the sheetname in quotes, should then work with spaces

do a search on db connection strings, should be able to find how to make line 2 the hdr, otherwise can you change the worksheet to put the hdr on line 1?