Getting Data From Excel



  • 1. Multi User to MSAccess 2000 Database on Novell Ver 6
    Hi We have been asked whether an application we have written is safe to use in a multi user environment which has Novell Ver 6 as it's central server. Our application uses an MSAccess 2000 Database as the backend database which is accessed via ADO and the Jet Engine. As we have no experience of Novell I though I would ask whether anyone knows if there are likely to be any problems with Record/File locking etc.. under this environment. Any advice you could give would be appreciated Cheers Stuart
  • 2. Run a stor proc and compile error:Type mismatch
    Where to begin..... Very simply, your code to set up the connection is invalid. Try this:- Set cnn = New ADODB.Connection cnn.ConnectionString = "yourconnectionstring" Then don't set the activeconnection for the command object until immediately before you execute it(i.e. after you've set up the parameters) .ActiveConnection = cnn You also don't need to format the dates, just use cdate & ado should take care of the rest - this is one of the chief advantages of using ado parameters, it avoids many of the common problems with dates. Hope this helps
  • 3. ADO versions incompatible
    Our company started developing software (VC 6) using ADO 2.5 and that worked fine until we installed ADO 2.6 on some computers. Then it became "illegal" to set a closed recordset pointer to null, i.e. pRs->Close(); pRs = NULL; throws an exception. Now when I tried out the .NET framework and the 2.7 version was installed, it became "illegal" to close the recordset (pRs->Close() throws an exception)! Why did you not make the new ADO versions backwards compatible, Microsoft? Code written for older versions should work even if the components are updated. Is there any easy way to fix these problems? /Nicklas

Getting Data From Excel

Postby Val Komissarov » Thu, 06 Nov 2003 19:45:35 GMT

I need to get data from an Excel speadsheet into my Access database using
ADO. Below is my procedure that does it. Question: I had to specify a named
range (Data1) in Excel spreadsheet since I don't know any other way to
select ALL records (.Source = "SELECT * FROM Data1"). Is there a way to
select all records in an unknown Excel spreadsheet without tampering with
the source Excel file?




Sub test()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim i As Integer

    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Data Source").Value = "X:\VMB-August.xls"
        .Properties("Extended Properties").Value = "Excel 8.0"
    End With
    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .Source = "SELECT * FROM Data1"
        Do Until .EOF
               Debug.Print rs.Fields(0).Value
    End With
    Set rs = Nothing
    Set cn = Nothing
End Sub

Re: Getting Data From Excel

Postby Val Mazur » Mon, 10 Nov 2003 14:17:57 GMT

Hi Val,

Yes, you can do this, but you would need to specify spreadsheet name. Check
next KBs with some samples

 http://www.**--****.com/ ;EN-US;257819

 http://www.**--****.com/ ;en-us;278973

 http://www.**--****.com/ ;en-us;295646

Val Mazur
Microsoft MVP
Check Virus Alert, stay updated


Getting data from Excel

Postby dmVkaWFq » Tue, 03 Feb 2004 20:21:06 GMT

I need help getting data from excel. I have 48 values in one column only to be read by my vb program.
is there a simple way to use that values straight from excel other than converting into a text array? thanks

Re: Getting data from Excel

Postby Mike Collier » Wed, 04 Feb 2004 00:39:01 GMT

> I need help getting data from excel.

You can use JET see topic:

Mike Collier
FREE ADO Inspector tool when you register at ADO forum.

Similar Threads:

1.Getting Data from Excel Graph onto New Speadsheet

I am trying to get the information that I have plotted, on a scatted graph, 
off onto the second sheet an splitting the information. I'm not too sure if 
this is the right place for this thread, but I am hoping that someone can 

The original data that is on the graph is in 3 columns. One has the company 
name, one is the current value and one is the predicted value.

I need this info to come off the graph or from the original data in 2 
fields. Customer and predicted value, but they need to be prioritized.

1 needs to be the one with the lowest current and highest potential.
2 needs to be the 2nd lowest current and 2nd highest on.
Then I need to get the highest current and highest on.

I know that what I require is quite complex and it may have to be via 
calculations, but I am hoping that excel can do this?

2.Getting data into Excel cells for OLE objects retuened from Access

Hello all,

I have a MS Query in Excel filtering and retrieving 
relevant data from MS Access. When the data returns, all 
BUT the column which refers to a OLE object datatype in 
Access returns values. 

What should I set the format in Excel to see the OLE 
objects dynamically.


3.Getting data from excel sheets on different machines ??

I need to copy and summarize certain data from excel sheets on various 
machines in a network to an excel sheet on my computer in the network. how 
can I possibly do it???

4.Getting Data to Excel spreadsheet from link having Flash support

5.fastest way of getting data into excel

Hi Everyone

I am writing an app at the moment which has to import a large amount of data (1m + cells) into excel and am currently using the excel object model to do this.  I am using the cells value property to do this i.e 'objsheet.Cells(1,1).value = x'

I have noticed that usings the cell object model seem to be very slow is there a faster way of getting the data into excel


6. getting data from excel worksheet in to a script

7. Getting data from excel sheets on different machines ??

8. Fastest way of getting data into excel

Return to ADO


Who is online

Users browsing this forum: No registered users and 20 guest