set function and variable file names

Ox1138

New Member
Joined
Feb 18, 2019
Messages
2
Hi All

Thanks for any help on this, I am fairly new and still learning.

I am trying to develop a macro that will open, copy, (and if possible close) from about 50 closed workbooks. I am currently using this code:

Dim wb As Workbook
Set wb = Workbooks.Open("C:\Users\Me\Path\AA.csv", True, True)
ThisWorkbook.Activate

But this only opens the file AA.csv, I have a variable "Live" which cycles through the names of the closed workbooks. However, everytime I try to change the Set wb to something that has a variable in it, I seem to fail.

For clarity - I want to be able to change "aa.csv" (or any longer part of the string if necessary) within the macro.

Thanks for any help you can offer.

Cheers
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,726
Office Version
2007
Platform
Windows
For example:

Code:
Sub OpenWb()
    Dim wb As Workbook
    Dim live As Variant, wPath As String
    
    wPath = "C:\Users\Me\Path\"
    live = Dir(wPath & "*.csv")
    Do While live <> ""
        Set wb = Workbooks.Open(wPath & live, True, True)
        ThisWorkbook.Activate
        '
        'here your code
        '
        live = Dir()
    Loop
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,726
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,159
Messages
5,466,994
Members
406,514
Latest member
jasonseebaluck

This Week's Hot Topics

Top