Parse Cell To Generate File Saveas name

[KurtWilliams]

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!

 

[6StringJazzer]

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.

   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]

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.

   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]

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:

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

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.