[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

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
0
dhamijap
Asked:
dhamijap
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now