Solved

Connecting to an excel sheet using ADO's

Posted on 2002-03-21
13
350 Views
Last Modified: 2013-11-23
Hi,

I have tried to connect to an excel workbook using ADO's, but when I try to set a recordset to a worksheet, I am getting an error because the Sheet names end with a $ (eg. Sheet1$,Sheet2$).  Connection is successful, problem comes when I try to open the worksheet.
0
Comment
Question by:riaz9
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 50 total points
ID: 6886166
Hi riaz9,

-Microsoft Knowledge Base articles can be found at:
-http://search.support.microsoft.com/kb/c.asp
-Choose to search by "specific article ID number"
-Enter the article number including the "Q"

Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO
Q246335 HOWTO: Transfer Data from ADO Recordset to Excel with Automation
Q257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
Q278973 SAMPLE: ExcelADO Shows How to Read/Write Data in Excel Workbooks

HTH:O)Bruintje
0
 
LVL 10

Expert Comment

by:Clif
ID: 6886226
Wrap the sheet name with square brackets  [Sheet1$]
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6886286
hi bruintje!, so active lately ;)
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6886300
yup have to broaden the skills, by picking up some new things from the guru's here ;)
0
 
LVL 1

Author Comment

by:riaz9
ID: 6888141
Thank you bruintje and clif - both your solutions work (essentially the [] was my missing link).  I just need a little extra help here - I have failed trying to create a new excel workbook using ADOX with the following code.

catCatalog.Create "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = "" & FileName & "";Extended Properties=Excel 8.0"

Can you tell me how to create a new empty excel workbook.
0
 
LVL 1

Author Comment

by:riaz9
ID: 6888157
note: I do not wish to use Excel Automation
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 44

Expert Comment

by:bruintje
ID: 6888219
could you change it to

    strConnection = "Data Source=" & App.Path & "\test.xls;" & "Extended Properties=""Excel 8.0"""

    cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cn.Open strConnection, "", ""

seems something with the extra "
0
 
LVL 1

Author Comment

by:riaz9
ID: 6920101
sorry for the delay - but, these links helped me.
0
 
LVL 1

Author Comment

by:riaz9
ID: 6920107
sorry for the delay - but, these links helped me.
0
 
LVL 1

Author Comment

by:riaz9
ID: 6920113
sorry for the delay - but, these links helped me.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6920137
glad i could help
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6920565
Then, why "B" grade?
0
 
LVL 1

Author Comment

by:riaz9
ID: 6922441
i tried to post the comment, that the extra " did not help me (but the web page had some problem - so thats why my earlier comment got posted thrice).  Since the answer by bruintje (and unfortunately the links of Microsoft) also did not solve my entire problem (that of being able to create a new sheet also, and the data written has a ' which according to Microsoft can cause problems when working with data later), thats why B grade. Sorry if it i under-graded the answer.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now