Excel set active Workbook

Question

I searched the forum and couldn't find an answer that would fit my problem. I am pretty new to Excel VBA and am having trouble activating a workbook I just opened. Directly below is the part that is causing me trouble.

So I press a button and it brings me to the file path, and I select a file to open. Every week this file has a different name and I am not really sure what it is until I open the file path and look at it.

Once I open it, I would like my macro to manipulate the data in the file and copy and paste into the workbook running the code. However when I run this macro and open the file it will not activate the newly opened workbook and runs the rest of the macro trying to manipulate the data in the original file.

I think I either need to open the file differently so that the workbook I just opened is the active one or figure out how to activate the newly opened workbook without knowing the file name. Thank you for your help.

Dim filepath As String
filepath = Environ("USERPROFILE") & "\Dropbox\On the go ordering"
Call Shell("explorer.exe" & " " & filepath, vbNormalFocus)


Range("A6:E500").Select

Sub on_the_go_button()

    Dim RANKER As Workbook

    Set RANKER = ThisWorkbook

    Dim filepath As String
    filepath = Environ("USERPROFILE") & "\Dropbox\On the go ordering"
    Call Shell("explorer.exe" & " " & filepath, vbNormalFocus)


    Range("A6:E500").Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$E$495").AutoFilter Field:=1, Criteria1:=RGB(213, _
        223, 248), Operator:=xlFilterCellColor
    Range("G1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RIGHT(RC[-6],8)=""Subtotal"",VALUE(LEFT(RC[-6],6)),"""")"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-4])"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-4])"
    Range("G1:I1").Select
        Selection.Copy
    Range("G1:I500").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Selection.Copy
    RANKER.Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("P1:Q74").Select
    Selection.Copy
    Sheets("Contest").Select
    Range("A3").Select

    ActiveCell.End(xlToRight).Select


    ActiveCell.Offset(0, 1).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

See [This](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Siddharth Rout, Aug 24 '16 at 05:32
Direclty work with objects. `Dim wb as workbook: Set wb = workbooks.open(FullFilePathWithName)` — Siddharth Rout, Aug 24 '16 at 05:33
You should use `Application.GetOpenFilename` to select the workbook to open. Your current macro just opens the folder and continues to run: it's not waiting for you to select a file. — Tim Williams, Aug 24 '16 at 05:33

Techie · Answer 1 · 2016-08-25T04:09:16.673

2

Try below...

 filepath = application.getopenfilename()
 Dim Wb as workbook
 Set Wb = workbooks.open(filepath)

Use WB as workbook object

edited Aug 25 '16 at 04:09

answered Aug 24 '16 at 05:46

Techie

181
4
14

THanks for the help and quick response, it worked perfectly. – Joe Penn Aug 24 '16 at 05:55

score 2 · Accepted Answer · answered Aug 24 '16 at 07:20

Because the OP specified a default path; I recommend using the Application.FileDialog(msoFileDialogFilePicker)

USAGE:

Dim WeeklyWorkbook

Set WeeklyWorkbook = getWeeklyWorkbook

If WeeklyWorkbook Is Nothing Then
    MsgBox "No file selected", vbInformation, "Action Cancelled"
    Exit Sub
End If

Function getWeeklyWorkbook() As Workbook

    Dim fDialog As FileDialog, result As Integer
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

    'Optional: FileDialog properties
    fDialog.AllowMultiSelect = False
    fDialog.Title = "Select a file"
    fDialog.InitialFileName = Environ("USERPROFILE") & "\Dropbox\On the go ordering"
    'Optional: Add filters
    fDialog.Filters.Clear
    fDialog.Filters.Add "Excel files", "*.xls, *.xlsx, *.xlsm"
    fDialog.Filters.Add "All files", "*.*"

    'Show the dialog. -1 means success!
    If fDialog.Show = -1 Then
        Set getWeeklyWorkbook = Workbooks.Open(fDialog.SelectedItems(1))
    End If

End Function

Reference: VBA FILEDIALOG – OPENING, SELECTING AND SAVING FILES AND FOLDERS

Thank you, that is exactly what I wanted, it opens up to exactly where I want to be. — Joe Penn, Aug 25 '16 at 02:15

Excel set active Workbook

2 Answers2

USAGE: