VBA EXCEL - open file and assign path to a new workbook path

Question

there. i'm trying to pass a file path for defining a new workbook's path... All seems fine until I get a "Subscript out of range" at the line:

Set rngS = Workbooks(wkbS).Range("myRange1")

I've reduced the original file name length, as well as for its folder directory name, etc... I've also switched type definitions between string, variant, etc... I'd appreciate any suggestions. txs a lot.

Sub myMain()

dim wkbS as string
dim rngS as range

wkbS = open_file_src
Debug.Print "w_source_path is : " & wkbS

Set rngS = Workbooks(wkbS).Range("myRange1")      
debug.print "Range rngS : " & rngS.address
End Sub


Function open_file_src() As String
   
    strFile_src = Application.GetOpenFilename(filefilter:="Excel files,*.x*", Title:="Select SOURCE file")
    Workbooks.Open Filename:=strFile_src
    Debug.Print "open_file_src PATH is : " & strFile_src
    
    open_file_src = strFile_src
        
End Function

There are some inconsistencies here: 1) wkbT is defined as a Workbook Collection object and you are trying to define it as Thisworkbook. 2) The line you mentioned tries to read a range from a closed workbook. You must first open it or use other techniques to do so. The subsequent line attempts to directly read a range from a Workbook object the way used to read one from a sheet. Some variables are not DIMmed. — ALeXceL
– ALeXceL, Commented Sep 11, 2022 at 16:34

ALeXceL · Accepted Answer · 2022-09-11 17:10:48Z

2

You have to pass the name of the target Workbook without it's path as it is open on Excel:

wkbS="ChoosedWorkbook.xls"

And then there are two ways to set rngS range:

Set rngS =  = Workbooks(wkbS).Names("myRange1").RefersToRange

or, passing the sheet name wich contains the target range:

Set rngS = Workbooks(wkbS).Sheets("theNameOfTheSheet").Range("myRange1")

The modified code to get the filename:

Function open_file_src() As String
   Dim strFile_src As String
    strFile_src = Application.GetOpenFilename(filefilter:="Excel files,*.x*", Title:="Select SOURCE file")
    Workbooks.Open Filename:=strFile_src
    Debug.Print "open_file_src PATH is : " & strFile_src
    
    open_file_src = Right(strFile_src, Len(strFile_src) - InStrRev(strFile_src, "\"))
        
End Function

edited Sep 11, 2022 at 17:10

answered Sep 11, 2022 at 17:01

ALeXceL

6597 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA EXCEL - open file and assign path to a new workbook path

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related