Excel - Make user opened file the active workbook

[TenDeadGoats]

Hi Guys,

I'm trying to write a macro that allows the user to select an excel file, which is then formatted a bunch and gets some graphs and charts added and then gets saved as a different name leaving the original document untouched. I'm really experiencing a lot of difficulty trying to get the user opened file to be the active workbook so that I can do all the formatting in it. The file name they will be opening is going to be different every time so I can't just hardcode in the name of the file.

Basic overview of what i want the code to do:

- Workbook 1(contains button for macro) : Press button, prompted to open workbook2 (containing the necessary data)

- Workbook2 is opened, all the tables/charts/formatting is automatically applied, workbook2 is saved as a new file name (workbook3) and then closed

- Final result: Workbook 1 is still open, workbook2 has been saved to a new name and closed

I want to make opened file selected by the user the active workbook so that I can do all the activeworkbook commands for the charts and tables and whatnot. If anyone had any suggestions that would be greatly appreciated!

Code I have so far:

'OPEN


'Display a Dialog Box that allows to select a single file.
'The path for the file picked will be stored in fullpath variable
With Application.FileDialog(msoFileDialogFilePicker)
'Makes sure the user can select only one file
.AllowMultiSelect = False
'Filter to just the following types of files to narrow down selection options
.Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
'Show the dialog box
.Show

'Store in fullpath variable
Dim fullpath As String
fullpath = .SelectedItems.Item(1)

End With

'It's a good idea to still check if the file type selected is accurate.
'Quit the procedure if the user didn't select the type of file we need.
If InStr(fullpath, ".xls") = 0 Then
Exit Sub
End If

'Open the file selected by the user
Workbooks.Open fullpath

.Activate -----> This is where I am having difficulties

'FORMAT THE NEW WORKBOOK


' Rename the current sheet
ActiveSheet.Name = "Commissions Data"


' Create, name, and color the other needed sheets
Sheets.Add(After:=Sheets("Commissions Data")).Name = "Client Distribution"
Sheets.Add(After:=Sheets("Client Distribution")).Name = "Issuer Distribution"


Sheets("Commissions Data").Tab.ColorIndex = 3
Sheets("Client Distribution").Tab.ColorIndex = 4
Sheets("Issuer Distribution").Tab.ColorIndex = 5

Sheets("Commissions Data").Select


'SAVE


'Copy activesheet to the new workbook
ActiveSheet.Copy
MsgBox "This new workbook will be saved as Consolidated Commissions Statements.xlsx"

'Save new workbook as MyWb.xls(x) into the folder where ThisWorkbook is stored
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Consolidated Commissions Statements", xlWorkbookDefault
MsgBox "It is saved as " & ActiveWorkbook.FullName & vbLf & "Press OK to close it"

' Close the saved copy
ActiveWorkbook.Close False

End Sub

This is my first time using this forum so I apologize if I have neglected any etiquette. Let me know if I can provide any additional info at all.

[paulked]

Hi and welcome to the forum.

When you open a workbook then that workbook automatically becomes the active workbook.

[TenDeadGoats]

Thanks for the reply Paul,

I would've thought that would be the way it would work as well, but for some reason the active workbook is remaining as the workbook that contains the original macro. I think this might be because I'm opening AND closing the workbook in the macro. Any thoughts on how to fix?

[paulked]

Doesn't do that for me. I commented out your .Activate and put in a message box to prove the active book, try it.

Sub OPENbook()


    'Display a Dialog Box that allows to select a single file.
    'The path for the file picked will be stored in fullpath variable
    With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show

        'Store in fullpath variable
        Dim fullpath As String
        fullpath = .SelectedItems.Item(1)

    End With

    'It's a good idea to still check if the file type selected is accurate.
    'Quit the procedure if the user didn't select the type of file we need.
    If InStr(fullpath, ".xls") = 0 Then
        Exit Sub
    End If

    'Open the file selected by the user
    Workbooks.Open fullpath

    '.Activate -----> This is where I am having difficulties

    MsgBox ActiveWorkbook.Name

    'FORMAT THE NEW WORKBOOK

    ' Rename the current sheet
    ActiveSheet.Name = "Commissions Data"


    ' Create, name, and color the other needed sheets
    Sheets.Add(After:=Sheets("Commissions Data")).Name = "Client Distribution"
    Sheets.Add(After:=Sheets("Client Distribution")).Name = "Issuer Distribution"


    Sheets("Commissions Data").Tab.ColorIndex = 3
    Sheets("Client Distribution").Tab.ColorIndex = 4
    Sheets("Issuer Distribution").Tab.ColorIndex = 5

    Sheets("Commissions Data").Select


    'SAVE


    'Copy activesheet to the new workbook
    ActiveSheet.Copy
    MsgBox "This new workbook will be saved as Consolidated Commissions Statements.xlsx"

    'Save new workbook as MyWb.xls(x) into the folder where ThisWorkbook is stored
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Consolidated Commissions Statements", xlWorkbookDefault
    MsgBox "It is saved as " & ActiveWorkbook.FullName & vbLf & "Press OK to close it"

    ' Close the saved copy
    ActiveWorkbook.Close False

End Sub

[paulked]

Your code is wrong at the bottom. Which sheet do you want to copy and to where?

[TenDeadGoats]

Hi Paul,

I think I've figured out where the problem in my code is. When the user selects the file to open, this does become the active workbook (you were right). However, the file the user is selecting to open contains the original data, so I don't want to change that file at all. I want to then open another new workbook using the macro (not the workbook the user selected) to make all the charts in so I can leave the original file containing the data untouched. So I actually don't want to make the file the user opens the active sheet, I want to make the new workbook that the macro opens the active workbook so that I can put all the charts in there. Does that make sense?

At the end I have 3 workbooks
1) original workbook containing macros (no changes after being run)
2) Workbook opened by the user containing the data to be used for charts (no changes after being run)
3) Output workbook opened by macro that will contain charts and be saved with a new name (want this one to be the activebook so I can run the commands)

Does that make sense? The problem right now is that all the formatting changes are happening in the workbook containing the data that I want to leave untouched.

[paulked]

Open the template book and then saveas. The template will not change and now you can carry out your changes to the new workbook which is active.

Sub OPENbook()


    'Display a Dialog Box that allows to select a single file.
    'The path for the file picked will be stored in fullpath variable
    With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show

        'Store in fullpath variable
        Dim fullpath As String
        fullpath = .SelectedItems.Item(1)

    End With

    'It's a good idea to still check if the file type selected is accurate.
    'Quit the procedure if the user didn't select the type of file we need.
    If InStr(fullpath, ".xls") = 0 Then
        Exit Sub
    End If

    'Open the file selected by the user
    Workbooks.Open fullpath
    
    'Save opened workbook as new workbook
    MsgBox "This new workbook will be saved as Consolidated Commissions Statements " & Format(Now, "dd mmm hh mm") & ".xlsx"
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Consolidated Commissions Statements " & Format(Now, "dd mmm hh mm"), xlWorkbookDefault
    
    'Now make your changes
    
    'FORMAT THE NEW WORKBOOK

    ' Rename the current sheet
    ActiveSheet.Name = "Commissions Data"


    ' Create, name, and color the other needed sheets
    Sheets.Add(After:=Sheets("Commissions Data")).Name = "Client Distribution"
    Sheets.Add(After:=Sheets("Client Distribution")).Name = "Issuer Distribution"


    Sheets("Commissions Data").Tab.ColorIndex = 3
    Sheets("Client Distribution").Tab.ColorIndex = 4
    Sheets("Issuer Distribution").Tab.ColorIndex = 5

    Sheets("Commissions Data").Select




    'Copy activesheet to the new workbook
    'ActiveSheet.Copy <<<<<<<<<<<<<<<<<<<<<<<<<<<This will copy the sheet to new workbook!!!


    ' Close the saved copy
    ActiveWorkbook.Save
    MsgBox "It is saved as " & ActiveWorkbook.FullName & vbLf & "Press OK to close it"
    ActiveWorkbook.Close 0

End Sub

A word of caution: A previously created workbook with this name will be over-written. To stop this I've put a time stamp on the file.

[TenDeadGoats]

Paul,

You are an absolute godsend. I have been battling with this problem for the better part of 2 days and you just solved EXACTLY what I needed done. Thank you so much, I really really appreciate you taking the time out of your day to help me!

I do have one final question. This is super minor, more of a housekeeping issue really. At this point I now have 3 workbooks open. Is there any way I can close the workbook containing the raw data after the macro runs so the only workbooks open are the one containing the macro and the one containing the output? This is a VERY minor issue, just want to make it look as professional as possible.

Thanks again Paul, you're the best!

[paulked]

You don't have 3 open, you changed the name of the opened book when you saveas'd so you only have the workbook you ran the macro from and the Activeworkbook that you are applying changes to... until it is saved and closed!

Run that code and at the end you'll only have the workbook you atarted with open (unless you have others open!)

[TenDeadGoats]

For some reason I do have all three open. I start with just the macro book open, and when I run the macro it leaves me with the the raw data file open as well as the new output file open. I think this is because when I open the first raw data file, it copies the data and then opens up the 3rd workbook to paste it into (where I want to do the charts). Leaving me with the raw data file open, and the new workbook it just opened to paste the data into. I think it might be happening because of the activesheet.copy part?

[paulked]

I commented that out, see above post #7 in red!!!

[paulked]

Your code is wrong at the bottom. Which sheet do you want to copy and to where?

From post 5 too!!!

[TenDeadGoats]

Hi Paul,

You're so right it hurts. I didn't quite understand what you had meant there due to my own ignorance. When I made that change it works perfectly. I guess this is because we don't actually need to copy the worksheet over if we are just saving it as a new document right?

Thanks for your patience Paul, I know I am very low level beginner.

Spencer

[paulked]

No worries, I'm still learning too

Couple of forum tips:

1. You can mark this solved (Thread Tools on the top tool bar) now it is sorted.
2. Any code you post, wrap it with code tabs (# on the editor menu), it makes the posts far easier to read and understand.

Happy to help