Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - closed workbook - Named range
Looked in the tips section and found the code at beneath... ..it doesn't return a bean. It's fine apart from the range part!!! Anyone got any ideas? Option Explicit Private Sub UserForm_Initialize() ' fill ListBox1 with data from a closed workbook ' can also be used from other applications to read data from an open workbook Dim tArray As Variant tArray = ReadDataFromWorkbook("C:\Test\Broker Disclosure\RD.xls", "LIST") FillListBox Me.ListBox1, tArray Erase tArray End Sub Private Function ReadDataFromWorkbook(SourceFile As String, _ SourceRange As String) As Variant ' requires a reference to the Microsoft ActiveX Data Objects library ' (menu Tools, References in the VBE) ' if SourceRange is a range reference: ' this function can only return data from the first worksheet in SourceFile ' if SourceRange is a defined name reference: ' this function can return data from any worksheet in SourceFile ' SourceRange must include the range headers ' examples: ' varRecordSetData = _ ReadDataFromWorkbook("C:\FolderName\SourceWbName.x ls", "A1:A21") ' varRecordSetData = _ ReadDataFromWorkbook("C:\FolderName\SourceWbName.x ls", "A1:B21") ' varRecordSetData = _ ReadDataFromWorkbook("C:\FolderName\SourceWbName.x ls", "DefinedRangeName") Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset Dim dbConnectionString As String dbConnectionString = _ "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile Set dbConnection = New ADODB.Connection On Error GoTo InvalidInput dbConnection.Open dbConnectionString ' open the database connection Set rs = dbConnection.Execute("[" & SourceRange & "]") On Error GoTo 0 ReadDataFromWorkbook = rs.GetRows ' returns a two dim array with all records in rs dbConnection.Close ' close the database connection rs.Close Set rs = Nothing Set dbConnection = Nothing On Error GoTo 0 Exit Function InvalidInput: MsgBox "The source file or source range is invalid!", _ vbExclamation, "Get data from closed workbook" Set rs = Nothing Set dbConnection = Nothing End Function Private Sub FillListBox(lb As MSForms.ListBox, RecordSetArray As Variant) ' fills lb with data from RecordSetArray Dim r As Long, c As Long With lb ..Clear For r = LBound(RecordSetArray, 2) To UBound(RecordSetArray, 2) ..AddItem For c = LBound(RecordSetArray, 1) To UBound(RecordSetArray, 1) ..List(r, c) = RecordSetArray(c, r) Next c Next r ..ListIndex = -1 ' no item selected End With End Sub -- MattShoreson ------------------------------------------------------------------------ MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472 View this thread: http://www.excelforum.com/showthread...hreadid=376003 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - closed workbook - Named range
sorted it -- MattShoreso ----------------------------------------------------------------------- MattShoreson's Profile: http://www.excelforum.com/member.php...nfo&userid=347 View this thread: http://www.excelforum.com/showthread.php?threadid=37600 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - closed workbook - Named range
Hey Matt, I am doing the same thing here but I have difficulty with the range
name as you did, could you tell me how you fixed it. I want my range to be "usedrange" of a worksheet. I've only been working with macros for a few months now so my programming abilities are a bit basic, Cheers "MattShoreson" wrote: sorted it. -- MattShoreson ------------------------------------------------------------------------ MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472 View this thread: http://www.excelforum.com/showthread...hreadid=376003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use indirect when referring to a named range in a closed | Excel Worksheet Functions | |||
INDIRECT and Named Ranges referencing closed workbook | Excel Worksheet Functions | |||
ListFillRange linked to named range in a closed remote file | Excel Programming | |||
referencing a named range from a closed workbook | Excel Programming | |||
Problem with named Range in ADO extract from Closed Excel File | Excel Programming |