Solved

Connecting to an excel sheet using ADO's

Posted on 2002-03-21
13
353 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

911 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

22 Experts available now in Live!

Get 1:1 Help Now