Home |
Search |
Today's Posts |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a closed wbook
Try an ADO solution...
'Requires reference to microsoft Active X Data Objects Lib 2.7 Public Sub QueryWorksheet() Dim Recordset As ADODB.Recordset Dim ConnectionString As String ConnectionString = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source= H:\Test3.xls;" & _ "Extended Properties=Excel 8.0;" Dim SQL As String ' Query based on the worksheet name. SQL = "SELECT * FROM [Sheet1$]" ' Query based on a sheet level range name. ' SQL = "SELECT * FROM [Sales$MyRange]" ' Query based on a specific range address. ' SQL = "SELECT * FROM [Sales$A1:E14]" ' Query based on a book level range name. ' SQL = "SELECT * FROM BookLevelName" Set Recordset = New ADODB.Recordset On Error GoTo Cleanup Call Recordset.Open(SQL, ConnectionString, _ CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, _ CommandTypeEnum.adCmdText) Call Sheet1.Range("A1").CopyFromRecordset(Recordset) Cleanup: If (Err.Number < 0) Then Debug.Print Err.Description End If If (Recordset.State = ObjectStateEnum.adStateOpen) Then Recordset.Close End If Set Recordset = Nothing End Sub "Geoff K" wrote: Thank you. But again, please read my question. Yes it is simple but that is for an open wbook. I want to get the last row from a CLOSED wbook. Geoff "Jeff" wrote: Returning the last used row is pretty simple, Here are a Function and Sub procedure examples Public Sub Geoff_K() Dim lRow As Long lRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row End Sub Public Function GetLastRow() As Long Dim lRow As Long lRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row GetLastRow = lRow End Function |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get values from 15 sheets in a Wbook | Excel Programming | |||
updating wbook from other... | Excel Programming | |||
transfering data from 2 wbook | Excel Programming | |||
transfering data value from 2 Wbook... | Excel Programming | |||
Read And Write On A Closed Wbook | Excel Programming |