Connecting to an excel sheet using ADO's


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

Hi riaz9,

-Microsoft Knowledge Base articles can be found at:
-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


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
Wrap the sheet name with square brackets  [Sheet1$]
Richie_SimonettiIT OperationsCommented:
hi bruintje!, so active lately ;)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

yup have to broaden the skills, by picking up some new things from the guru's here ;)
riaz9Author Commented:
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.
riaz9Author Commented:
note: I do not wish to use Excel Automation
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 "
riaz9Author Commented:
sorry for the delay - but, these links helped me.
riaz9Author Commented:
sorry for the delay - but, these links helped me.
riaz9Author Commented:
sorry for the delay - but, these links helped me.
glad i could help
Richie_SimonettiIT OperationsCommented:
Then, why "B" grade?
riaz9Author Commented:
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.
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

From novice to tech pro — start learning today.