Using Excel sheet as input in VB6

hes: here is the question

Data1.RecordSource = "SheetName" & "$"

How can I use the sheet tab name dynamicall in the applicaiton and what does "$" do at the end?

dhamijap
dhamijapAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

adityauCommented:
First open an excel file using the following code.

Dim a As Excel.Application
Set a = New Excel.Application
a.Visible = True
a.Workbooks.Open 'Existing fiile name

Then use the following statement to use teh sheet name dynamically

a.Sheets(SheetName)
0
ericsonCommented:
try this:

Dim db As Database
Dim ws As Workspace
Dim rs As Recordset
   
Set ws = DBEngine.CreateWorkspace("EXCEL", "Admin", "")

Set db = ws.OpenDatabase("", dbDriverNoPrompt, False, "Excel 5.0;DATABASE=FILE.XLS;")
   
Set rs = db.OpenRecordset("dsheet$", dbOpenDynaset, dbDenyRead, dbOptimistic)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dhamijapAuthor Commented:
adityau:
 I am using database connection but connecting excel. So i donot want to open the excel application.

Thanks  anyway

dhamijap

ericson:

In your code below:

Set rs = db.OpenRecordset("dsheet$", dbOpenDynaset, dbDenyRead, dbOptimistic)

I am getting the follwing error:

Error Number 3125
the database engine can't fine dsheet$

Please note that I am using Excel 8.0 and the sheet i want to use is "sheet1" better yet can i determine the name of first sheet dynamicall?

Please give sample code if possible.

Thanks

dhamija
0
ericsonCommented:
try this:

Dim db As Database
Dim ws As Workspace
Dim rs As Recordset
     
'This sample uses DAO
Set ws = DBEngine.CreateWorkspace("EXCEL", "Admin", "")

'create or open a workbook
Set db = ws.OpenDatabase("", dbDriverNoPrompt, False, "Excel 8.0;DataBase=NewFile.xls")
     
'Crete a new sheet
db.Execute "create table TestSheet (Code int, Description varchar(100))"
   
'open the sheet
Set rs = db.OpenRecordset("TestSheet", dbOpenDynaset, dbDenyRead, dbOptimistic)

rs.AddNew
rs(0) = 1
rs(1) = "Teste de Insercao"
rs.Update

rs.Close

db.Close

ws.Close
0
dhamijapAuthor Commented:
ericson:

I got it working thank a lot.

dhamijap
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.