TenDeadGoats
05-04-2020, 10:22 AM
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.
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.