asked on

Loop throuh range names in closed workbook and get data

The attached code is the begining of a query in VBA process that I need to do the following: Each of the rangen ames in the source workbook beging with RAT. I need this code to loop through all the range names LIKE "RAT*", bring in the indicated coumns and then place them below the data that was returned for the previous query. I am starting with how to I tell this code, which is in BONDPROFILE workbook, to loop through the ranges in a source workbook and in taht source workbook, put the results on a worksheet call DataSource? Basically I need to control the second workbok from the first. This data is pulled from 50 worksheets taht have many more columns, but I only need a subset of the data in the "combining" worksheet-Datasource. This worksheet will be used in another process but taht is later. Right now, I just need to figure out how to loop through the range names in the source workbook.

Public Sub QueryWorkSheet(strWorkbook As String, strType As String)
On Error GoTo ErrorHandler
Dim rst As ADODB.Recordset
Dim strCnn As String
Dim strSQL As String
Dim strFullPath As String
Dim strDestination  As String
Dim strRangeName    As String   'Name of range in source workbook to pull data
Dim wks             As Worksheet
Dim wkb             As Workbook

If strType = "RATING" Then
    strDestination = "A8"
ElseIf strType = "SECTOR" Then
    strDestination = "J8"

strFileExportPathSumm = ThisWorkbook.Worksheets("Settings").Range("FOLDER_PATH_SUMMARY") ' Summary Reports/General Folder, Range name on Settings worksheet, Settings!$I$5
strFullPath = strFileExportPathSumm & strWorkbook


strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strFullPath & ";" & _
        "Extended Properties=Excel 8.0;"
With Workbooks(strWbkName) 'Current month's Green Package destination workbook
For Each wks In Workbooks(strWbkName).Worksheets
    If wks.Name Like "RATING*" Then
                For Each rngName In .Range(strRangeName)
                    If rngName Like "RAT*" Then
                        strSQL = "SELECT ClientCode , [%NotionalScaling], [%NotionalBench], " & _
                              "SpreadDurationContributionPort, SpreadDurationContributionBench " & _
                              "FROM " & rngName & " "
                         Set rst = New ADODB.Recordset
                         Call rst.Open(strSQL, strCnn, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)
                         Call Range(strDestination).CopyFromRecordset(rst) 'Need to dynamcially determin last cell
                    End If
                    Next
                Next
rst.Close
Set rst = Nothing

Exit_ErrorHandler:
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & " Description: " & Err.Description & " Procedure: QueryWorksheet"

If (rst.State = ObjectStateEnum.adStateOpen) Then
rst.Close
End If
Set rst = Nothing
    Resume Exit_ErrorHandler
End Sub

Open in new window

Rory Archibald

You cannot refer to a closed workbook using the Workbooks collection. Are the named ranges dynamic? If so, you will have to open the source workbook first and you may suffer memory leak issues if you use ADO against open workbooks.

Sandra Smith

ASKER

Ok, I can have the code be sure the workbook is open. Don't care about memory leaks - than again, not sure what the effect would be. The range names are assigned in code when teh porcess runs, They are basically the workbook names, but all special characters removed. That is, if a workbook's name is RATING-MOON_F, the - and _ are removed for a range name of the data on that worksheet of RATINGMOONF.

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Sandra Smith

ASKER

Yes, worksheet names. All the client worksheets are in one workbook. and I guess running out of memory would be a bit of an issue! So, in my controlling workbok (BONDPROFILE) I have the workbook CLIENTMASTER with all the data and both are opened. So, what's next?

Sandra Smith

ASKER

SOrry, that was misleading, the controlling workbook with the VBA code is BONDPROFILE. The workbook (with client worksheets) its creates is the CLIENTMASTER. So, from BONDPRFOLIE, I need to query each of the worksheets (in CLIENTMASTER) that start with the word RATING, query the range for that worksheet - I only want four out of the 89 columns - and place the results of that query into the DataSource worksheet also located in the CLIENTMASTER.

Rory Archibald

OK, well assuming the range names always exist for sheets beginning with RATING and the naming convention is consistent, we shouldn't need to actually open the workbook - as long as the range names refer to fixed addresses (rather than using formulas). Is that the case?

Also, are there field names in the top row of the range?

A sample of the source data would help - only one or two worksheets and no real data required but a couple of rows of dummy info and the actual headers would be useful.

Sandra Smith

ASKER

I have been doing some research and I discovered that I should be using NAME rather than a Range object for the loop. So I changed to the attached code. However, when it gets to the statement
If rngName Like "*RAT*" Then
It is returning the following:"=RATING-MOON!$A$1:$BK$40" rather than a range name of RATMOON. DO I need to create code to clear out the extraneous characters to get from "=RATING-MOON!$A$1:$BK$40" to RATMOON?

Public Sub QueryWorkSheet(strWbkName As String, strType As String)
On Error GoTo ErrorHandler
Dim rst As ADODB.Recordset
Dim strCnn As String
Dim strSQL As String
Dim strFullPath As String
Dim strDestination  As String
Dim rngName As Name
Dim strRangeName    As String   'Name of range in source workbook to pull data
Dim wks             As Worksheet
Dim wkb             As Workbook

If strType = "RATING" Then
    strDestination = "A8"
ElseIf strType = "SECTOR" Then
    strDestination = "J8"
End If

strFileExportPathSumm = ThisWorkbook.Worksheets("Settings").Range("FOLDER_PATH_SUMMARY") ' Summary Reports/General Folder, Range name on Settings worksheet, Settings!$I$5
strFullPath = strFileExportPathSumm & strWbkName

strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strFullPath & ";" & _
        "Extended Properties=Excel 8.0;"
With Workbooks(strWbkName) 'Current month's Green Package destination workbook
    For Each wks In Workbooks(strWbkName).Worksheets
        If wks.Name Like "RATING*" Then
            For Each rngName In wks.Names
                If rngName Like "*RAT*" Then 'THis line give me =RATING-MOON!$A$1:$BK$40 rather than just RATMOON??
                    strSQL = "SELECT ClientCode , [%NotionalScaling], [%NotionalBench], " & _
                          "SpreadDurationContributionPort, SpreadDurationContributionBench " & _
                          "FROM " & rngName & " "
                     Set rst = New ADODB.Recordset
                     Call rst.Open(strSQL, strCnn, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)
                     Call Range(strDestination).CopyFromRecordset(rst) 'Need to dynamcially determin last cell
                End If
             Next
        End If
    Next
End With
rst.Close
Set rst = Nothing

Exit_ErrorHandler:
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & " Description: " & Err.Description & " Procedure: QueryWorksheet"

    If (rst.State = ObjectStateEnum.adStateOpen) Then
    rst.Close
    End If
Set rst = Nothing
    Resume Exit_ErrorHandler
End Sub

Open in new window

Sandra Smith

ASKER

Let me dummy up some data, have a meeting and will be back in about three hours. Yes, there are column headers in one row at the top of the named range, which are used in the select statement. The ranges are dynamically determined when the code runs, but they the time the process gets to this procedure, the ranges have been identified and are fixed at that point.

Sandra Smith

ASKER

Roya, atually, this solved itself. I realized that the clling procedure is a loop. I changed the called procedure to require the name of the range and VIOLA! WORKS! Since there was no need for a solution on this question, I am going to accept your explanation of the memory leak as that is very important!