Using ADO Recordsets to pull data from a closed Excel WB.

B

Bryan44

I am using code from Ron de Bruin (thanks very much by the way!!!) to pull
data from a closed Workbook residing on a server on our network. This closed
workbook holds project historical data used in the workbook I am making the
call from. This closed workbook holds dates, integers, floating point
numbers, and text. The ADO code Ron provides examples of pulls the dates,
integers, and floating point numbers but not the text. The code is as
follows:

Option Explicit

Public Sub GetData(SourceFile As Variant, _
SourceSheet As String, _
SourceRange As String, _
DestinationRange As Range, _
GetHeader As Boolean, _
UseHeaderRow As Boolean)

' Setup variables
Dim RecordSetData As ADODB.Recordset
Dim DBConnectString As String
Dim SQLCommandObject As String
Dim LoopCounter As Long

' Build the SQL Command to pull the data from the source DB.
SQLCommandObject = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$
& "];"

' If GetHeader determines whether or not to pull the header information
off the top row of the
' range and put it, if it is true, into the DestinationRange.
If GetHeader = False Then
DBConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
DBConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
End If

On Error GoTo ErrorHandler

Set RecordSetData = New ADODB.Recordset
RecordSetData.Open SQLCommandObject, DBConnectString, adOpenForwardOnly,
adLockReadOnly, adCmdText

' Check to make sure we received data and copy the data
If Not RecordSetData.EOF Then

If GetHeader = False Then
DestinationRange.Cells(1, 1).CopyFromRecordset RecordSetData
Else
'Add the GetHeader cell in each column if the last argument is
True
If UseHeaderRow Then
For LoopCounter = 0 To RecordSetData.Fields.Count - 1
DestinationRange.Cells(1, 1 + LoopCounter).Value =
RecordSetData.Fields(LoopCounter).Name
Next LoopCounter
DestinationRange.Cells(2, 1).CopyFromRecordset RecordSetData
Else
DestinationRange.Cells(1, 1).CopyFromRecordset RecordSetData
End If
End If

Else
MsgBox "No records returned from : " & SourceFile, vbCritical
End If

' Clean up our Recordset object.
RecordSetData.Close
Set RecordSetData = Nothing
Exit Sub

ErrorHandler:
MsgBox "FPE Tool Error: The file name, Sheet name or Range is not valid
of : " & SourceFile, _
vbExclamation, "Error"
On Error GoTo 0
End Sub


I have changed the format of the closed workbook text to "General" then to
"Text" but it doesn't make any difference. The ADO call will not pull text
for what ever reason. Curious though, it will pull the first row if you set
getheader and useheader to true but concatinates sequential integers on the
end of the second column (i.e., name, name1, name2, .... name256). Perhaps
someone can help me understand what is not right or what I need to change to
get an entire set of data that includes all of the information stored in the
closed workbook. BTW, this call works terrific on the local network and even
acceptable accross a VPN interface.

Sincerely,

Bryan44
 
R

Ron de Bruin

Hi Bryan44

Have you read this on my webpage
http://www.rondebruin.nl/ado.htm

In a Database you cannot mix data types, a column must be all numbers or all text.
If there are different data types in the column ADO will copy only the Data type that have the majority.

Another option is to open the files with code
http://www.rondebruin.nl/copy3.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


Bryan44 said:
I am using code from Ron de Bruin (thanks very much by the way!!!) to pull
data from a closed Workbook residing on a server on our network. This closed
workbook holds project historical data used in the workbook I am making the
call from. This closed workbook holds dates, integers, floating point
numbers, and text. The ADO code Ron provides examples of pulls the dates,
integers, and floating point numbers but not the text. The code is as
follows:

Option Explicit

Public Sub GetData(SourceFile As Variant, _
SourceSheet As String, _
SourceRange As String, _
DestinationRange As Range, _
GetHeader As Boolean, _
UseHeaderRow As Boolean)

' Setup variables
Dim RecordSetData As ADODB.Recordset
Dim DBConnectString As String
Dim SQLCommandObject As String
Dim LoopCounter As Long

' Build the SQL Command to pull the data from the source DB.
SQLCommandObject = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$
& "];"

' If GetHeader determines whether or not to pull the header information
off the top row of the
' range and put it, if it is true, into the DestinationRange.
If GetHeader = False Then
DBConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
DBConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
End If

On Error GoTo ErrorHandler

Set RecordSetData = New ADODB.Recordset
RecordSetData.Open SQLCommandObject, DBConnectString, adOpenForwardOnly,
adLockReadOnly, adCmdText

' Check to make sure we received data and copy the data
If Not RecordSetData.EOF Then

If GetHeader = False Then
DestinationRange.Cells(1, 1).CopyFromRecordset RecordSetData
Else
'Add the GetHeader cell in each column if the last argument is
True
If UseHeaderRow Then
For LoopCounter = 0 To RecordSetData.Fields.Count - 1
DestinationRange.Cells(1, 1 + LoopCounter).Value =
RecordSetData.Fields(LoopCounter).Name
Next LoopCounter
DestinationRange.Cells(2, 1).CopyFromRecordset RecordSetData
Else
DestinationRange.Cells(1, 1).CopyFromRecordset RecordSetData
End If
End If

Else
MsgBox "No records returned from : " & SourceFile, vbCritical
End If

' Clean up our Recordset object.
RecordSetData.Close
Set RecordSetData = Nothing
Exit Sub

ErrorHandler:
MsgBox "FPE Tool Error: The file name, Sheet name or Range is not valid
of : " & SourceFile, _
vbExclamation, "Error"
On Error GoTo 0
End Sub


I have changed the format of the closed workbook text to "General" then to
"Text" but it doesn't make any difference. The ADO call will not pull text
for what ever reason. Curious though, it will pull the first row if you set
getheader and useheader to true but concatinates sequential integers on the
end of the second column (i.e., name, name1, name2, .... name256). Perhaps
someone can help me understand what is not right or what I need to change to
get an entire set of data that includes all of the information stored in the
closed workbook. BTW, this call works terrific on the local network and even
acceptable accross a VPN interface.

Sincerely,

Bryan44
Click to expand...
 
B

Bryan44

Ron,

No, you caught me. I missed that but it surely does answer the question as
to why it won't pull the data. Wonder why they chose not to mix types as
most every database mixes types within a record. I guess the only other
option is to open the file read-only, as the End-Users need the data but have
no need to write to the file, as opposed to the ADO functionality. It sure
works very fast though so the performance was very good!

What is the difference between the ADO and DAO services?

Thanks,

Bryan

Ron de Bruin said:
Hi Bryan44

Have you read this on my webpage
http://www.rondebruin.nl/ado.htm

In a Database you cannot mix data types, a column must be all numbers or all text.
If there are different data types in the column ADO will copy only the Data type that have the majority.

Another option is to open the files with code
http://www.rondebruin.nl/copy3.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


Bryan44 said:
I am using code from Ron de Bruin (thanks very much by the way!!!) to pull
data from a closed Workbook residing on a server on our network. This closed
workbook holds project historical data used in the workbook I am making the
call from. This closed workbook holds dates, integers, floating point
numbers, and text. The ADO code Ron provides examples of pulls the dates,
integers, and floating point numbers but not the text. The code is as
follows:

Option Explicit

Public Sub GetData(SourceFile As Variant, _
SourceSheet As String, _
SourceRange As String, _
DestinationRange As Range, _
GetHeader As Boolean, _
UseHeaderRow As Boolean)

' Setup variables
Dim RecordSetData As ADODB.Recordset
Dim DBConnectString As String
Dim SQLCommandObject As String
Dim LoopCounter As Long

' Build the SQL Command to pull the data from the source DB.
SQLCommandObject = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$
& "];"

' If GetHeader determines whether or not to pull the header information
off the top row of the
' range and put it, if it is true, into the DestinationRange.
If GetHeader = False Then
DBConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
DBConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
End If

On Error GoTo ErrorHandler

Set RecordSetData = New ADODB.Recordset
RecordSetData.Open SQLCommandObject, DBConnectString, adOpenForwardOnly,
adLockReadOnly, adCmdText

' Check to make sure we received data and copy the data
If Not RecordSetData.EOF Then

If GetHeader = False Then
DestinationRange.Cells(1, 1).CopyFromRecordset RecordSetData
Else
'Add the GetHeader cell in each column if the last argument is
True
If UseHeaderRow Then
For LoopCounter = 0 To RecordSetData.Fields.Count - 1
DestinationRange.Cells(1, 1 + LoopCounter).Value =
RecordSetData.Fields(LoopCounter).Name
Next LoopCounter
DestinationRange.Cells(2, 1).CopyFromRecordset RecordSetData
Else
DestinationRange.Cells(1, 1).CopyFromRecordset RecordSetData
End If
End If

Else
MsgBox "No records returned from : " & SourceFile, vbCritical
End If

' Clean up our Recordset object.
RecordSetData.Close
Set RecordSetData = Nothing
Exit Sub

ErrorHandler:
MsgBox "FPE Tool Error: The file name, Sheet name or Range is not valid
of : " & SourceFile, _
vbExclamation, "Error"
On Error GoTo 0
End Sub


I have changed the format of the closed workbook text to "General" then to
"Text" but it doesn't make any difference. The ADO call will not pull text
for what ever reason. Curious though, it will pull the first row if you set
getheader and useheader to true but concatinates sequential integers on the
end of the second column (i.e., name, name1, name2, .... name256). Perhaps
someone can help me understand what is not right or what I need to change to
get an entire set of data that includes all of the information stored in the
closed workbook. BTW, this call works terrific on the local network and even
acceptable accross a VPN interface.

Sincerely,

Bryan44
Click to expand...
Click to expand...
 
B

Bryan44

Ron,

Just in case it wasn't too clear, thank you very much for you help. I have
used your site a few times to see "how" to do things concept wise. It is
very helpful when more seasoned developers do things like you've done to help
those of us who are rather new to VBA.

Most sincerely,

Bryan

Bryan44 said:
Ron,

No, you caught me. I missed that but it surely does answer the question as
to why it won't pull the data. Wonder why they chose not to mix types as
most every database mixes types within a record. I guess the only other
option is to open the file read-only, as the End-Users need the data but have
no need to write to the file, as opposed to the ADO functionality. It sure
works very fast though so the performance was very good!

What is the difference between the ADO and DAO services?

Thanks,

Bryan

Ron de Bruin said:
Hi Bryan44

Have you read this on my webpage
http://www.rondebruin.nl/ado.htm

In a Database you cannot mix data types, a column must be all numbers or all text.
If there are different data types in the column ADO will copy only the Data type that have the majority.

Another option is to open the files with code
http://www.rondebruin.nl/copy3.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


Bryan44 said:
I am using code from Ron de Bruin (thanks very much by the way!!!) to pull
data from a closed Workbook residing on a server on our network. This closed
workbook holds project historical data used in the workbook I am making the
call from. This closed workbook holds dates, integers, floating point
numbers, and text. The ADO code Ron provides examples of pulls the dates,
integers, and floating point numbers but not the text. The code is as
follows:

Option Explicit

Public Sub GetData(SourceFile As Variant, _
SourceSheet As String, _
SourceRange As String, _
DestinationRange As Range, _
GetHeader As Boolean, _
UseHeaderRow As Boolean)

' Setup variables
Dim RecordSetData As ADODB.Recordset
Dim DBConnectString As String
Dim SQLCommandObject As String
Dim LoopCounter As Long

' Build the SQL Command to pull the data from the source DB.
SQLCommandObject = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$
& "];"

' If GetHeader determines whether or not to pull the header information
off the top row of the
' range and put it, if it is true, into the DestinationRange.
If GetHeader = False Then
DBConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
DBConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
End If

On Error GoTo ErrorHandler

Set RecordSetData = New ADODB.Recordset
RecordSetData.Open SQLCommandObject, DBConnectString, adOpenForwardOnly,
adLockReadOnly, adCmdText

' Check to make sure we received data and copy the data
If Not RecordSetData.EOF Then

If GetHeader = False Then
DestinationRange.Cells(1, 1).CopyFromRecordset RecordSetData
Else
'Add the GetHeader cell in each column if the last argument is
True
If UseHeaderRow Then
For LoopCounter = 0 To RecordSetData.Fields.Count - 1
DestinationRange.Cells(1, 1 + LoopCounter).Value =
RecordSetData.Fields(LoopCounter).Name
Next LoopCounter
DestinationRange.Cells(2, 1).CopyFromRecordset RecordSetData
Else
DestinationRange.Cells(1, 1).CopyFromRecordset RecordSetData
End If
End If

Else
MsgBox "No records returned from : " & SourceFile, vbCritical
End If

' Clean up our Recordset object.
RecordSetData.Close
Set RecordSetData = Nothing
Exit Sub

ErrorHandler:
MsgBox "FPE Tool Error: The file name, Sheet name or Range is not valid
of : " & SourceFile, _
vbExclamation, "Error"
On Error GoTo 0
End Sub


I have changed the format of the closed workbook text to "General" then to
"Text" but it doesn't make any difference. The ADO call will not pull text
for what ever reason. Curious though, it will pull the first row if you set
getheader and useheader to true but concatinates sequential integers on the
end of the second column (i.e., name, name1, name2, .... name256). Perhaps
someone can help me understand what is not right or what I need to change to
get an entire set of data that includes all of the information stored in the
closed workbook. BTW, this call works terrific on the local network and even
acceptable accross a VPN interface.

Sincerely,

Bryan44
Click to expand...
Click to expand...
Click to expand...
 
A

Alok

Bryan,

You noted "Wonder why they chose not to mix types as
most every database mixes types within a record".

I just want to clarify that ADO will work if different fields or columns are
different data types but not if the same field or column has different data
types in different rows. This is what Ron De Bruin's site is really saying.
Also you will agree that this behavior is fairly reasonable.

Alok

Bryan44 said:
Ron,

No, you caught me. I missed that but it surely does answer the question as
to why it won't pull the data. Wonder why they chose not to mix types as
most every database mixes types within a record. I guess the only other
option is to open the file read-only, as the End-Users need the data but have
no need to write to the file, as opposed to the ADO functionality. It sure
works very fast though so the performance was very good!

What is the difference between the ADO and DAO services?

Thanks,

Bryan

Ron de Bruin said:
Hi Bryan44

Have you read this on my webpage
http://www.rondebruin.nl/ado.htm

In a Database you cannot mix data types, a column must be all numbers or all text.
If there are different data types in the column ADO will copy only the Data type that have the majority.

Another option is to open the files with code
http://www.rondebruin.nl/copy3.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


Bryan44 said:
I am using code from Ron de Bruin (thanks very much by the way!!!) to pull
data from a closed Workbook residing on a server on our network. This closed
workbook holds project historical data used in the workbook I am making the
call from. This closed workbook holds dates, integers, floating point
numbers, and text. The ADO code Ron provides examples of pulls the dates,
integers, and floating point numbers but not the text. The code is as
follows:

Option Explicit

Public Sub GetData(SourceFile As Variant, _
SourceSheet As String, _
SourceRange As String, _
DestinationRange As Range, _
GetHeader As Boolean, _
UseHeaderRow As Boolean)

' Setup variables
Dim RecordSetData As ADODB.Recordset
Dim DBConnectString As String
Dim SQLCommandObject As String
Dim LoopCounter As Long

' Build the SQL Command to pull the data from the source DB.
SQLCommandObject = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$
& "];"

' If GetHeader determines whether or not to pull the header information
off the top row of the
' range and put it, if it is true, into the DestinationRange.
If GetHeader = False Then
DBConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
DBConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
End If

On Error GoTo ErrorHandler

Set RecordSetData = New ADODB.Recordset
RecordSetData.Open SQLCommandObject, DBConnectString, adOpenForwardOnly,
adLockReadOnly, adCmdText

' Check to make sure we received data and copy the data
If Not RecordSetData.EOF Then

If GetHeader = False Then
DestinationRange.Cells(1, 1).CopyFromRecordset RecordSetData
Else
'Add the GetHeader cell in each column if the last argument is
True
If UseHeaderRow Then
For LoopCounter = 0 To RecordSetData.Fields.Count - 1
DestinationRange.Cells(1, 1 + LoopCounter).Value =
RecordSetData.Fields(LoopCounter).Name
Next LoopCounter
DestinationRange.Cells(2, 1).CopyFromRecordset RecordSetData
Else
DestinationRange.Cells(1, 1).CopyFromRecordset RecordSetData
End If
End If

Else
MsgBox "No records returned from : " & SourceFile, vbCritical
End If

' Clean up our Recordset object.
RecordSetData.Close
Set RecordSetData = Nothing
Exit Sub

ErrorHandler:
MsgBox "FPE Tool Error: The file name, Sheet name or Range is not valid
of : " & SourceFile, _
vbExclamation, "Error"
On Error GoTo 0
End Sub


I have changed the format of the closed workbook text to "General" then to
"Text" but it doesn't make any difference. The ADO call will not pull text
for what ever reason. Curious though, it will pull the first row if you set
getheader and useheader to true but concatinates sequential integers on the
end of the second column (i.e., name, name1, name2, .... name256). Perhaps
someone can help me understand what is not right or what I need to change to
get an entire set of data that includes all of the information stored in the
closed workbook. BTW, this call works terrific on the local network and even
acceptable accross a VPN interface.

Sincerely,

Bryan44
Click to expand...
Click to expand...
Click to expand...
 
R

Ron de Bruin

Hi Bryan44

Note different types in a column not in a record, see also the response from Alok.
In a normal database each column have one type of data in it so there will be no problem

ADO is in most cases faster and have more options as far as I know.


--
Regards Ron de Bruin
http://www.rondebruin.nl


Bryan44 said:
Ron,

No, you caught me. I missed that but it surely does answer the question as
to why it won't pull the data. Wonder why they chose not to mix types as
most every database mixes types within a record. I guess the only other
option is to open the file read-only, as the End-Users need the data but have
no need to write to the file, as opposed to the ADO functionality. It sure
works very fast though so the performance was very good!

What is the difference between the ADO and DAO services?

Thanks,

Bryan

Ron de Bruin said:
Hi Bryan44

Have you read this on my webpage
http://www.rondebruin.nl/ado.htm

In a Database you cannot mix data types, a column must be all numbers or all text.
If there are different data types in the column ADO will copy only the Data type that have the majority.

Another option is to open the files with code
http://www.rondebruin.nl/copy3.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


Bryan44 said:
I am using code from Ron de Bruin (thanks very much by the way!!!) to pull
data from a closed Workbook residing on a server on our network. This closed
workbook holds project historical data used in the workbook I am making the
call from. This closed workbook holds dates, integers, floating point
numbers, and text. The ADO code Ron provides examples of pulls the dates,
integers, and floating point numbers but not the text. The code is as
follows:

Option Explicit

Public Sub GetData(SourceFile As Variant, _
SourceSheet As String, _
SourceRange As String, _
DestinationRange As Range, _
GetHeader As Boolean, _
UseHeaderRow As Boolean)

' Setup variables
Dim RecordSetData As ADODB.Recordset
Dim DBConnectString As String
Dim SQLCommandObject As String
Dim LoopCounter As Long

' Build the SQL Command to pull the data from the source DB.
SQLCommandObject = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$
& "];"

' If GetHeader determines whether or not to pull the header information
off the top row of the
' range and put it, if it is true, into the DestinationRange.
If GetHeader = False Then
DBConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
DBConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
End If

On Error GoTo ErrorHandler

Set RecordSetData = New ADODB.Recordset
RecordSetData.Open SQLCommandObject, DBConnectString, adOpenForwardOnly,
adLockReadOnly, adCmdText

' Check to make sure we received data and copy the data
If Not RecordSetData.EOF Then

If GetHeader = False Then
DestinationRange.Cells(1, 1).CopyFromRecordset RecordSetData
Else
'Add the GetHeader cell in each column if the last argument is
True
If UseHeaderRow Then
For LoopCounter = 0 To RecordSetData.Fields.Count - 1
DestinationRange.Cells(1, 1 + LoopCounter).Value =
RecordSetData.Fields(LoopCounter).Name
Next LoopCounter
DestinationRange.Cells(2, 1).CopyFromRecordset RecordSetData
Else
DestinationRange.Cells(1, 1).CopyFromRecordset RecordSetData
End If
End If

Else
MsgBox "No records returned from : " & SourceFile, vbCritical
End If

' Clean up our Recordset object.
RecordSetData.Close
Set RecordSetData = Nothing
Exit Sub

ErrorHandler:
MsgBox "FPE Tool Error: The file name, Sheet name or Range is not valid
of : " & SourceFile, _
vbExclamation, "Error"
On Error GoTo 0
End Sub


I have changed the format of the closed workbook text to "General" then to
"Text" but it doesn't make any difference. The ADO call will not pull text
for what ever reason. Curious though, it will pull the first row if you set
getheader and useheader to true but concatinates sequential integers on the
end of the second column (i.e., name, name1, name2, .... name256). Perhaps
someone can help me understand what is not right or what I need to change to
get an entire set of data that includes all of the information stored in the
closed workbook. BTW, this call works terrific on the local network and even
acceptable accross a VPN interface.

Sincerely,

Bryan44
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem when copying a range from closed workbooks (ADO) 4
Follow up question. Get value of closed file. 5
SQL String 1
Copy Data from Excel 2007 XLSM workbook to Excel 2003 XLS workbook using ADO 2
Error With ADO (Starting to pull my hair out) 2
export excel data to access table using ADO 0
Compile Error (ADO) Ron De Bruin 2
ADO access to excel workbook on website 1

Top