VBA to open a new workbook and save as the value from a cell in another workbook

[Benzoli7]

Can anyone help me with some code that will open a new workbook and save it in another folder with a file name that is derived from the value of a cell in another workbook?

The value that will create the name for the new workbook is located in cell A4 of a sheet named "GRAPHS" in a workbook named "AAA DATA".

The file path for the new workbook will be: S:\Trans\AAA Data\"CELL VALUE"

Thanks so much for your help.

 

[JoeMo]

Assuming the name of the workbook in cell A4 includes the file extension (.xls, .xlsx, ...)

Sub MakeNewBook()
Dim wB As Workbook
Dim nPath As String
nPath = "S:\Trans\AAA Data\" & ThisWorkbook.Sheets("GRAPHS").Range("A4").Value
Set wB = Workbooks.Add
With wB
    .SaveAs Filename:=nPath
End With
End Sub

 

[Benzoli7]

Thanks Jo. That worked perfectly.

 

[Benzoli7]

What would be the easiest way to refer to this new workbook. I'm guessing since its name is a cell in another workbook I should give that range some kind of name? If so, how do I do that?

The rest of this macro is going to switch back and forth many times between this new workbook and the one mentioned in the original post.

Thanks again.

 

[JoeMo]

The Dim statement in the code I posted (Dim wB as Workbook) together with the line Set wB = Workbooks.Add has established wB as an object. Thereafter you can simply refer to wB and Excel will know you are referring to the new workbook. For example, wB.Activate will activate the new workbook if it is not currently active.

 

[Benzoli7]

Thanks again Joe.

One more question for you.

I want to publish this new workbook as a PDF file and give it the same name as the workbook that your code creates. I tried to (actual name) code that I got from the macro recorder with the cell referencing code that you provided earlier. Can you tell me how to manipulate this to make both the excel and pdf files have the same name created from the same cell in the original book.


The code below is what the recorder gave me. The part that says "2011-9-29" needs to be placed with a reference to that original cell.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "O:\Transportation\Ben\TEST\2011-9-29.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True

Thanks again for you help.

 

[JoeMo]

Assuming the sheet "GRAPHS" is the one that contains the file name in cell A4 try this: (note that if A4 includes the file extension (.xls, .xlsx, ...) it will be included in the pdf file name. if that's not what you want let me know.)

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="O:\Transportation\Ben\TEST\" & _
    ThisWorkbook.Sheets("GRAPHS").Range("A4").Value & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True