asked on

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

adityau

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)

ASKER CERTIFIED SOLUTION

ericson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dhamijap

ASKER

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

ericson

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

dhamijap

ASKER

ericson:

I got it working thank a lot.

dhamijap