+ Reply to Thread
Results 1 to 2 of 2

Help to use Sheet name to read other workbooks

Hybrid View

  1. #1
    Registered User
    Join Date
    09-24-2013
    Location
    Brazil
    MS-Off Ver
    Office 2010
    Posts
    12

    Help to use Sheet name to read other workbooks

    Hi Guys, I really need help with the code below.
    Basically i have many other workbooks with names like 2110, 4685 , 3345 etc. All workbooks are identical, with the same structure.
    I have other (matrix) that contain information about all the other workbooks consolidated.Also inside i have some worksheets with the same name (2110,4685,3345).
    So basically, the code below is in the Matrix and in my mind i can use it to get information from all other workbooks (2110, 4685 , 3345 ) using the worksheet name of the Matrix.
    My problem : Actually i have one code to read each workbook. What can i do on this code code to get all informations from the workbooks and fill the MATRIX ?
    I know that i need to change this part (path = ActiveWorkbook.Path & "\2110.xlsm" ) but i have no idea how can i do it.

    Sub BuscaSQL1()
        Dim ConecaoPlan As New ADODB.Connection
        Dim rsConsulta As New ADODB.Recordset
        Dim path As String
        Dim Roda As Integer
        Dim sql As String
    
        path = ActiveWorkbook.Path & "\2110.xlsm"
        
        ConecaoPlan.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & Caminho & ";Extended Properties=Excel 8.0;"  '";Extended Properties=Excel 12.0 Xml;HDR=YES;"Extended Properties=Excel 8.0
        ConecaoPlan.Open
    
        Range("B10").Select
        
        Do While ActiveCell.Value <> "Cash Receipts"
            sql = "Select * From [MontaBase$] Where Dado1 Like '" & ActiveCell.Value & "'"
            rsConsulta.Open sql, ConecaoPlan, adOpenKeyset, adLockOptimistic
                    
            If rsConsulta.RecordCount > 0 Then
                For Roda = 2 To 65 Step 5
                'Range("FU10").Select
                    Cells(ActiveCell.Row, ActiveCell.Column + Roda).Value = rsConsulta!Dado2
                    rsConsulta.MoveNext
                Next
            End If
            rsConsulta.Close
            Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
        Loop
        Set ConecaoPlan = Nothing
        Set rsConsulta = Nothing
    End Sub

    Thanks for your time and sorry about my bad english.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help to use Sheet name to read other workbooks

    Hi,

    YOU'll need to hold the workbook names in a list somewhere then use a loop to process the list. So suppose the list is named say "MyWorkbooks" and contains numbers like 2110, 4685..etc then.

    'your Dim variables
    Dim x as Long
    
    For x =1 to Range("MyWorkbooks").Cells.Count
        path = ActiveWorkbook.Path & Cstr(Range("MyWorkbooks").Cells(x,1)) & ".xlsm"
    
    'your code
    
    
    Next x
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help to use Sheet name to read other workbooks
    By vidaLL in forum Excel General
    Replies: 2
    Last Post: 02-21-2018, 06:38 AM
  2. [SOLVED] Read through workbooks in a folder
    By Christoffer_A in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-27-2014, 09:39 AM
  3. Workbooks attached to emails come as READ ONLY
    By Hook in forum Excel General
    Replies: 3
    Last Post: 01-10-2013, 06:23 PM
  4. Why do workbooks open in READ ONLY format
    By Hook in forum Excel General
    Replies: 2
    Last Post: 05-10-2012, 01:25 PM
  5. Workbooks open in Read Only mode
    By Hook in forum Excel General
    Replies: 6
    Last Post: 03-14-2012, 11:27 PM
  6. Read-Only Workbooks
    By John Abbot in forum Excel General
    Replies: 2
    Last Post: 04-09-2007, 01:50 PM
  7. When I open two workbooks, the second one is read-only
    By Gary Watson in forum Excel General
    Replies: 0
    Last Post: 08-10-2006, 03:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1