Solved

Connecting to an excel sheet using ADO's

Posted on 2002-03-21
13
355 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can we place a tooltip on the actual vb6 form 5 42
How to build JSON File in Delphi 6 3 34
Graphics32 under Delphi 10.1 Berlin 2 67
Tidtcpserver listening on multiports? 1 27
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
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 Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

777 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