Solved

Connecting to an excel sheet using ADO's

Posted on 2002-03-21
13
365 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

690 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