Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Connecting to an excel sheet using ADO's

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
riaz9
Asked:
riaz9
  • 6
  • 4
  • 2
  • +1
1 Solution
 
bruintjeCommented:
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
 
ClifCommented:
Wrap the sheet name with square brackets  [Sheet1$]
0
 
Richie_SimonettiIT OperationsCommented:
hi bruintje!, so active lately ;)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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