select and open a workbook using filedialog and run a macro in that newly open workbook immediately

Question

I have a workbook with a button that launches a filedialog, after opening the new workbook I want to be able to run a set of macro immediately. The code that I use for opening the file is:

Sub openFile()

Dim SelectedFileItem As String
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogOpen)
    With fDialog
    'Custom dialog title
    .Title = "Seleccione uno o varios archivos"
    .AllowMultiSelect = False
    'Set the file path
    .InitialFileName = "C:\Users\Public"
    'Filters for file types allowed
    .Filters.Clear
    .Filters.Add "Excel files", "*.xlsx"
    If .Show = -1 Then
    'if user clicks OK
    SelectedFileItem = .SelectedItems(1)
    'Open the selected file
    Workbooks.Open (SelectedFileItem)
    Else
    'if user clicks Cancel
    End If
    End With
End Sub

and the set of macros I want to run immediately after selecting and opening the new workbook is:

Sub ReemplazarDotComma()

Dim ws_num As Integer
ws_num = ThisWorkbook.Worksheets.Count
Dim i As Integer

For i = 1 To ws_num
    ThisWorkbook.Worksheets(i).Activate
    Range("Y2:Y70").Select
        ActiveWindow.SmallScroll Down:=-60
        Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Next
End Sub

Sub Convertir_Variant()

Dim ws_num As Integer
ws_num = ThisWorkbook.Worksheets.Count
Dim i As Integer
Dim j As Integer
For i = 1 To ws_num
    ThisWorkbook.Worksheets(i).Activate
    For j = 2 To 70
        Cells(j, 25) = ConvertToVariant(Cells(j, 25))
    Next
Next
End Sub

`I want to run immediately after selecting and opening the new workbook...` So what is the problem that you are facing? — Siddharth Rout, Mar 04 '20 at 12:46
When you are opening a workbook running a macro from one workbook then the opened workbook becomes activeworkbook. So, you can run all those macros in the original workbook. But instead of mentioning thisworkbook mention activeworkbook. — Naresh, Mar 04 '20 at 12:47
the set of macros don't recognize the new workbook and runs in the first workbook — gmark, Mar 04 '20 at 12:48
gmark. store the newly opened workbook in an object for example `Set wb = Workbooks.Open (SelectedFileItem)` and then use `wb` instead of `ThisWorkbook` in your macros — Siddharth Rout, Mar 04 '20 at 12:50
@SiddharthRout.. Thats great. Learnt something again. Thanks. — Naresh, Mar 04 '20 at 12:53
Also using `wb` as mentoned above will negate the need to use `Activeworkbook/Select/Activate`. You can direclty work with the relevant object using `With Wb... End With` as shown [HERE](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Siddharth Rout, Mar 04 '20 at 12:53

select and open a workbook using filedialog and run a macro in that newly open workbook immediately

0 Answers0