Parse Cell To Generate File Saveas name

KurtWilliams

New Member
Joined
Jun 21, 2017
Messages
3
Good Mornig,

I have some limited VBA experience, mostly through forum posts and recording but I haven't been able to figure this one out. I am trying to write a macro to take an excel file, save it to a static location and name it based on only SOME of the contents in a cell. THEN I want the macro to trim out some sensitive information and save it again to a DIFFERENT static location with a similar name but appended with " - Public"

The cell I need to target is always the same format but has varying contents for dates, city and panel, it is a merged cell B2-O2, and the contents are similar to this (including the carriage returns):

Daily Schedule
Date: 13-Mar-2020
City: 99
Panel: 77987 - Detroit Area Representatives

What I would like it to do is isolate "Daily Schedule&" "&Panel:77987 and then append that with " - Panel Copy" as the name to save the first copy, then have my code cut out columns K & L and then save again but this time change the file name so that the appended part is " - Public Copy"

Anyone able to help me with writing the VBA to parse the cell and set the "save as" names? Thanks in advance!
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
700
Office Version
  1. 365
Platform
  1. Windows
I got a little lost in your description. You are mixing code into your description; it would be easier to just show what file name you want. Given the sample data you showed, it looks like you want this filename:

"Daily Schedule Panel:77987 - Panel Copy.xlsx"

":" is illegal in a Windows file name so you have to rethink that one.

I don't know what you mean by "cut out columns K & L". Your description doesn't say what in those columns.

Here is some code that will give you the first file name. It replaces the ":" with "-" but you can make it any legal file name character.

VBA Code:
   Dim V As Variant
   Dim FileName As String   

   V = Split([B2], Chr(10))
   
   FileName =  V(0) & " " & Replace(Mid(V(3), 1, InStr(8, V(3), " ")), ":", "-") & " - Panel Copy"

One last thing: Merged cells are almost always a bad idea, even worse when VBA is involved. You mentioned cutting columns K and L but they are merged in B2:O2 so cannot be addressed in the code.
 

KurtWilliams

New Member
Joined
Jun 21, 2017
Messages
3
I got a little lost in your description. You are mixing code into your description; it would be easier to just show what file name you want. Given the sample data you showed, it looks like you want this filename:

"Daily Schedule Panel:77987 - Panel Copy.xlsx"

":" is illegal in a Windows file name so you have to rethink that one.

I don't know what you mean by "cut out columns K & L". Your description doesn't say what in those columns.

Here is some code that will give you the first file name. It replaces the ":" with "-" but you can make it any legal file name character.

VBA Code:
   Dim V As Variant
   Dim FileName As String  

   V = Split([B2], Chr(10))
  
   FileName =  V(0) & " " & Replace(Mid(V(3), 1, InStr(8, V(3), " ")), ":", "-") & " - Panel Copy"

One last thing: Merged cells are almost always a bad idea, even worse when VBA is involved. You mentioned cutting columns K and L but they are merged in B2:O2 so cannot be addressed in the code.
Thanks for your response. It helped me get over the first hurdle. Trust me, I loath merged cells but unfortunately a lot of the files I deal with are exported from a custom database with merged cells. I have managed to record/edit/write the code to unmerge and get rid of the content I wanted eliminated and then re-merge the cells. Now the next two challenges:

1. If I want to specify a directory where I want the file to be stored, is that simply an addition to the: FileName = argument you provided?

2. The code in its current form is below, if I want to add another instruction to tell it to save again, after executing all these instructions, but this time with a different filename and possibly to a different location how would I go about this?

3. If I want to learn how to do all this properly do you have an online course/resource you'd recommend?

Sub Schedule_Save_and_Adjust()
'
' Schedule_Save_and_Adjust Macro
' Macro to Save PARP Panel Schedule in a Specific Location with a name determined by Panel Information & Date, then Edit out the Conference Chair & Participant Information and save again as a "Public Copy" in a different location.
'

Dim V As Variant
Dim FileName As String

V = Split([B2], Chr(10))

FileName = V(0) & " " & Replace(Mid(V(3), 1, InStr(8, V(3), " ")), ":", "-") & " - Panel Copy"

ActiveWorkbook.SaveAs FileName

Cells.Select
Selection.UnMerge
Columns("K:K").Select
Selection.Delete
Columns("L:L").Select
Selection.Delete
Columns("L:L").Select
Selection.Delete
Range("B1:K2").Select
Selection.Merge
Range("K3").Select
Columns("K:K").EntireColumn.AutoFit
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
700
Office Version
  1. 365
Platform
  1. Windows
1. If I want to specify a directory where I want the file to be stored, is that simply an addition to the: FileName = argument you provided?

Yes, you can add the directory up front, such as adding this line:

VBA Code:
FileName = "C:\Jeff\Documents\Stuff\" & FileName

2. The code in its current form is below, if I want to add another instruction to tell it to save again, after executing all these instructions, but this time with a different filename and possibly to a different location how would I go about this?

Use SaveAs again with whatever new location and file name you want

VBA Code:
FileName = "C:\Jeff\Documents\New Location\" & "New File Name.xlsx"
ActiveWorkbook.SaveAs FileName

3. If I want to learn how to do all this properly do you have an online course/resource you'd recommend?
That's a tough one because I have not learned just from one source. I have a computer science degree and 40 years in the industry, so I had a head start when I learned VBA, which was required for a business class in my master's program. I got started in VBA with an excellent book by John Walkenbach. There are also a lot of web sites that are great for reference--looking up a particular thing--but I haven't found them as good for tutorials. One great resource is Ron de bruin. Another is Chip Pearson, who passed away tragically a couple of years ago but his site is still up.

 

Watch MrExcel Video

Forum statistics

Threads
1,119,005
Messages
5,575,520
Members
412,672
Latest member
Tupelo1984
Top