VBA: Having ChDir filepath based on vlookup

[StuartHall]

Hey guys-

Any help appreciated. I have combo box user form, and depending on the user selection, a vlookup determines the appropriate filepath for my ChDir.

How can I set ChDir equal to that cell value? Having difficulties.

Thanks-

 

[AlphaFrog]

Your details are vague.

Is the result of the VLookup a complete path e.g.; C:\Myfolder\

Something like this may work.

ChDir Sheets("Sheet1").Range("A1").Value

 

[StuartHall]

Spot on - thank you

 

[Rick Rothstein]

Any help appreciated. I have combo box user form, and depending on the user selection, a vlookup determines the appropriate filepath for my ChDir.

How can I set ChDir equal to that cell value? Having difficulties.

Just out of curiosity, what are you doing that you think you need to use the ChDir function? Offhand, I cannot of any situation where you would need to physically change the active directory (you should be able to use a full path to what folder or file you are referring to).

 

[StuartHall]

In all honesty, I'm learning VBA in a trial by fire style - so I highly doubt that what I'm doing is the most efficient.

What I am trying to do is this:

I have 5 companies, each with 5 separate excel files detailing pertinent info.
I'd like to automate a summary workbook where I can choose a specific company from a drop down list, vba will bring me to that company's specific file directory where I can pick and choose whichever of the 5 excel files I need to summarize. Not overly complicated but difficult to explain.

Next steps would be to copy specific sheets from each of the selected files into a new summary workbook - preferably without opening each file. With the help of AlphaFrog, I was able to correctly locate my excel files based on company selection.

If you have a moment or any advice to spare, it'd be much appreciated. At the moment, just using my recorder, and modifying on an ad hoc basis.

 

[Rick Rothstein]

In all honesty, I'm learning VBA in a trial by fire style - so I highly doubt that what I'm doing is the most efficient.

What I am trying to do is this:

I have 5 companies, each with 5 separate excel files detailing pertinent info.
I'd like to automate a summary workbook where I can choose a specific company from a drop down list, vba will bring me to that company's specific file directory where I can pick and choose whichever of the 5 excel files I need to summarize. Not overly complicated but difficult to explain.

Next steps would be to copy specific sheets from each of the selected files into a new summary workbook - preferably without opening each file. With the help of AlphaFrog, I was able to correctly locate my excel files based on company selection.

Efficient or not, it would be most helpful if you showed us your code.

 

[StuartHall]

Of course -

Sub ManualStudiesConsolidation()
  Dim Filter As String, Title As String, msg As String
    Dim i As Integer, FilterIndex As Integer
    Dim Filename As Variant
    
    ' File filters
    Filter = "Excel Files (*.xlsm),*.xlsm," & _
            "All Files (*.*),*.*"
    '   Default filter to *.*
        FilterIndex = 3
    ' Set Dialog Caption
    Title = "Select File(s) to Open"
    ' Select Start Drive & Path
    ChDrive ("J")
    ChDir Sheets("Sheet1").Range("f7").Value
    
        With Application
        ' Set File Name Array to selected Files (allow multiple)
        Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True)
        ' Reset Start Drive/Path
        ChDrive (Left(.DefaultFilePath, 1))
        ChDir (.DefaultFilePath)
    End With
    ' Exit on Cancel
    If Not IsArray(Filename) Then
        MsgBox "No file was selected."
        Exit Sub
    End If
    
    For i = LBound(Filename) To UBound(Filename)
    Workbooks.Open Filename:= _
    
 End Sub
[\CODE]

 

[Rick Rothstein]

Of course -

ChDrive (Left(.DefaultFilePath, 1))
ChDir (.DefaultFilePath)
[\CODE][/QUOTE]
I'll address your original question shortly, but first, let me address a habit that you must learn to break or you will end up with what seems like unexplainable errors as you go forward in your programming career. When you call a subroutine that takes parameters, you can do so two different ways...

SubroutineName ArgumentList

or

Call SubroutineName(ArgumentList)

The syntax [B]requires[/B] the parentheses [B]only[/B] for the second method. If the argument list consists of only one argument, then you can surround the argument with parentheses [B]BUT[/B] if the argument list consists of two or more individual arguments, then surrounding the list with parentheses will generate an error. Why? It has to do with how VB treats parentheses that are [B]not[/B] required by syntax... it considers them to contain an expression that it must evaluate. A single argument (which can even possibly be an expression) can be evaluated (if it is not an expression, it just evaluates as itself, otherwise the expression is evaluated); however, a comma separated list of items or expressions has no meaning to VB's evaluation engine and so it will generate an error if asked to evaluate it. Now, to get to the above two code lines from the code you posted... ChDrive and ChDir are technically built-in VB statements, but underneath it all, the are subroutines that have arguments (it is just VB provided them to you instead of you having to create them), so they follow the same rules as for subroutines that I outlined above. The bad habit I am attempting to break you of... do not use parentheses around "things" in VB unless required by syntax or unless needed to clarify the order that parts of an expression need to be calculated in.

 

[StuartHall]

Rick - do you know how to copy specific sheets from each of the selected files into a new summary workbook?