CODEWITHSUNDEEP
vbaexcel
Ross Bradley
Ross Bradley

Reputation: 13

Referencing a workbook file path found in another sub (VBA)

I am running into this issue where I import and open a workbook in the first sub but want to reference it in the second but I'm not sure how. Right now it is a static name (I denoted the line with a few single quotes).

I tried referencing my variable "filename" but because it has to be sheet within the workbook I'm not positive how to do that. Thank you in advance!

Sub Procedure1()
    Dim Filt As String
    Dim filterindex As Integer
    Dim title As String
    Dim filename As Variant


    Filt = "Comma Seperated Files (*.csv),*.csv"    
    filterindex = 1


    title = "Select a File to Import"

    filename = Application.GetOpenFilename _
        (FileFilter:=Filt, _
        filterindex:=filterindex, _
        title:=title)


    If filename = False Then
        MsgBox "No file was selected"
        Exit Sub
    End If

    Workbooks.Open filename
End Sub

Sub Procedure2()
    Dim CurrentWS As Worksheet
    Set CurrentWS = ActiveSheet

    Dim SourceWS As Worksheet
    Set SourceWS = Workbooks("cedar.csv").Worksheets(1)''''''''''''''''''''''
    Dim SourceHeaderRow As Integer: SourceHeaderRow = 1
    Dim SourceCell As Range

    Dim TargetWS As Worksheet
    Set TargetWS = Workbooks("Prototype.xlsm").Worksheets(1)
    Dim TargetHeader As Range
    Set TargetHeader = TargetWS.Range("A1:AX1")

    Dim RealLastRow As Long
    Dim SourceCol As Integer


    SourceWS.Activate
    For Each Cell In TargetHeader
        If Cell.Value <> "" Then
            Set SourceCell = Rows(SourceHeaderRow).Find _
                (Cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
            If Not SourceCell Is Nothing Then
                SourceCol = SourceCell.Column
                RealLastRow = Columns(SourceCol).Find("*", LookIn:=xlValues, _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                If RealLastRow > SourceHeaderRow Then
                    Range(Cells(SourceHeaderRow + 1, SourceCol), Cells(RealLastRow, _
                        SourceCol)).Copy
                    TargetWS.Cells(2, Cell.Column).PasteSpecial xlPasteValues
                End If
            End If
        End If
    Next

    CurrentWS.Activate
    Workbooks("Prototype.xlsm").Sheets(1).Range("A1").Select
End Sub

Upvotes: 1

Views: 312

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

Typically you would pass the workbook as an argument to the second Sub:

Sub One()
    Dim wb As Workbook
    Set wb = Workbooks.Open(somePath)
    Two wb
End sub

Sub Two(wb As Workbook)
    With wb.sheets(1)
        'work with sheet
    End with
End sub

EDIT: I notice you don't actually call Two from One, so an alternative (but not as robust) way might be:

Dim wb As Workbook 'Global variable

Sub One()
    Set wb = Workbooks.Open(somePath)
End sub

Sub Two()
    With wb.sheets(1) 'use the global variable
        'work with sheet
    End with
End sub

Upvotes: 2

Related Questions