I am trying to figure how to use ADO to retrieve/store data into closed
workbook to get hopefully significant performance enhancement what I
currently do (open and close 100 workbooks)

I looked at Ron's code for retrieving data

http://www.rondebruin.nl/ado.htm

(Thanks Ron).

My question: is it possible to find out what the last row and column is
in the closed workbook.?

My other rookie question is:

Can I store a formula into a cell of a closed workbook using ADO. I am
assuming that if this is possible then the value will only be determined
when the workkbook is opened, or is there another way.

THank you for any suggestions

kurb

kurb wrote ...

I am trying to figure how to use ADO to retrieve/store data into closed
workbook to get hopefully significant performance enhancement what I
currently do (open and close 100 workbooks)
My question: is it possible to find out what the last row and column is
in the closed workbook.?

Using ADO on Excel data:
- You can find out the name of the last column.
- You can find the last value in a column.
- You can count the number of columns and the number of rows.
- If you know where the table begins on the sheet (e.g. cell A1), you
can use the row count and column count to determine the address of the
last cell in the last column.
- By trial and error, you could find out the address of the top left
cell of the table but you would probably lose the performance
advantage.

Can I store a formula into a cell of a closed workbook using ADO. I am
assuming that if this is possible then the value will only be determined
when the workkbook is opened

If you tried to insert a formulas into a cell it would be seen as
plain text, even when it was opened in the Excel UI. You'd need to
're-enter' the cell contents in some way e.g. click into the formula
bar and click out again.

Jamie.

--

Thank you very much.

I always know where the table beigins so thats ok.

I could not find any method for recordset (in the book that I have)
related to row count and column count
Appreciate a little more clarification please.

On the formula issue, I think that I have to do the calculations on a
local worksheet and then store with ADO, though I will lose on performance.

Thanks
Kurb

Jamie Collins wrote:

kurb wrote ...



I am trying to figure how to use ADO to retrieve/store data into closed
workbook to get hopefully significant performance enhancement what I
currently do (open and close 100 workbooks)
My question: is it possible to find out what the last row and column is
in the closed workbook.?



Using ADO on Excel data:
- You can find out the name of the last column.
- You can find the last value in a column.
- You can count the number of columns and the number of rows.
- If you know where the table begins on the sheet (e.g. cell A1), you
can use the row count and column count to determine the address of the
last cell in the last column.
- By trial and error, you could find out the address of the top left
cell of the table but you would probably lose the performance
advantage.



Can I store a formula into a cell of a closed workbook using ADO. I am
assuming that if this is possible then the value will only be determined
when the workkbook is opened



If you tried to insert a formulas into a cell it would be seen as
plain text, even when it was opened in the Excel UI. You'd need to
're-enter' the cell contents in some way e.g. click into the formula
bar and click out again.

Jamie.

--

kurb wrote ...

I always know where the table beigins so thats ok.

I could not find any method for recordset (in the book that I have)
related to row count and column count
Appreciate a little more clarification please.

Money where mouth is:

Sub test()

Const FULL_FILENAME As String = "" & _
"C:\Tempo\db.xls"

Dim strLastColName As String
strLastColName = LastColumnName( _
FULL_FILENAME, "Sheet1$")

MsgBox strLastColName

Dim vntLastColLastval As Variant
vntLastColLastval = LastValueInColumn( _
FULL_FILENAME, "Sheet1$", _
strLastColName)

If IsNull(vntLastColLastval) Then
MsgBox "(Value is null)"
Else
MsgBox CStr(vntLastColLastval)
End If

Dim lngCols As Long
lngCols = ColumnCount( _
FULL_FILENAME, "Sheet1$")
MsgBox CStr(lngCols)

Dim lngRows As Long
lngRows = RowCount( _
FULL_FILENAME, "Sheet1$")
MsgBox CStr(lngRows)

End Sub

Public Function LastColumnName( _
ByVal FullFilename As String, _
ByVal TableName As String _
) As String

Dim Con As Object
Dim rs As Object
Dim strCon As String

Const CONN_STRING As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<FULL_FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

' Build connection string
strCon = CONN_STRING
strCon = Replace(strCon, _
"<FULL_FILENAME", FullFilename)

' Open connection to workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.CursorLocation = 3 ' client-side
.ConnectionString = strCon

.Open

' Get column schema details
Set rs = .OpenSchema(4, _
Array(Empty, Empty, TableName, Empty))

End With


With rs

.ActiveConnection = Nothing
Con.Close

.Sort = "ORDINAL_POSITION DESC"
LastColumnName = _
.Fields("COLUMN_NAME").Value

End With

End Function

Public Function LastValueInColumn( _
ByVal FullFilename As String, _
ByVal TableName As String, _
ByVal ColumnName As String _
) As Variant

Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strSql1 As String

Const CONN_STRING As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<FULL_FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

Const SQL As String = "" & _
"SELECT <COL_NAME FROM [<TABLE_NAME];"

' Build connection string
strCon = CONN_STRING
strCon = Replace(strCon, _
"<FULL_FILENAME", FullFilename)

' Build sql
strSql1 = SQL
strSql1 = Replace(strSql1, "<TABLE_NAME", TableName)
strSql1 = Replace(strSql1, "<COL_NAME", ColumnName)

' Open connection to workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.CursorLocation = 3 ' client-side
.ConnectionString = strCon

.Open
Set rs = .Execute(strSql1)

End With

With rs

.ActiveConnection = Nothing
Con.Close

.MoveLast

LastValueInColumn = _
.Fields(0).Value

End With

End Function

Public Function ColumnCount( _
ByVal FullFilename As String, _
ByVal TableName As String _
) As Long

Dim Con As Object
Dim rs As Object
Dim strCon As String

Const CONN_STRING As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<FULL_FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

' Build connection string
strCon = CONN_STRING
strCon = Replace(strCon, _
"<FULL_FILENAME", FullFilename)

' Open connection to workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.CursorLocation = 3 ' client-side
.ConnectionString = strCon

.Open

' Get column schema details
Set rs = .OpenSchema(4, _
Array(Empty, Empty, TableName, Empty))

End With

With rs

.ActiveConnection = Nothing
Con.Close

ColumnCount = _
.RecordCount

End With

End Function


Public Function RowCount( _
ByVal FullFilename As String, _
ByVal TableName As String _
) As Variant

Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strSql1 As String

Const CONN_STRING As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<FULL_FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

Const SQL As String = "" & _
"SELECT COUNT(*) FROM [<TABLE_NAME];"

' Build connection string
strCon = CONN_STRING
strCon = Replace(strCon, _
"<FULL_FILENAME", FullFilename)

' Build sql
strSql1 = SQL
strSql1 = Replace(strSql1, "<TABLE_NAME", TableName)

' Open connection to workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.CursorLocation = 3 ' client-side
.ConnectionString = strCon

.Open
Set rs = .Execute(strSql1)

End With

With rs

.ActiveConnection = Nothing
Con.Close

.MoveLast

RowCount = _
.Fields(0).Value

End With

End Function


Jamie.

--

Hi Kurb

Open the files and do the things you want
http://www.rondebruin.nl/copy3.htm

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


"kurb" wrote in message ...


I am trying to figure how to use ADO to retrieve/store data into closed workbook to get hopefully significant performance
enhancement what I currently do (open and close 100 workbooks)

I looked at Ron's code for retrieving data

http://www.rondebruin.nl/ado.htm

(Thanks Ron).

My question: is it possible to find out what the last row and column is in the closed workbook.?

My other rookie question is:

Can I store a formula into a cell of a closed workbook using ADO. I am assuming that if this is possible then the value will only
be determined when the workkbook is opened, or is there another way.

THank you for any suggestions

kurb

Thanks for the response, appreciate it.

I may have got this wrong, but does this work for closed workbooks.?

I was looking for an ADO solution, not only for performance gain, but
also because of setting up a simple relational database later.

But if there are other ways of manipulating closed workbooks (eg
inserting rows, formulas) I would be interested in following up on that.

Thanks
Kurb




Ron de Bruin wrote:

Hi Kurb

Open the files and do the things you want
http://www.rondebruin.nl/copy3.htm

If you want to add formulas in the workbooks go for the Open method
It is also fast.

More ADO examples you can find here
http://www.erlandsendata.no/english/...php?t=envbadac

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


"kurb" wrote in message ...
Thanks for the response, appreciate it.

I may have got this wrong, but does this work for closed workbooks.?

I was looking for an ADO solution, not only for performance gain, but also because of setting up a simple relational database
later.

But if there are other ways of manipulating closed workbooks (eg inserting rows, formulas) I would be interested in following up
on that.

Thanks
Kurb




Ron de Bruin wrote:

Hi Kurb

Open the files and do the things you want
http://www.rondebruin.nl/copy3.htm