VBA Saveas Reference problem

[rplohocky]

Hello,
Can someone look at this and let me know if this is built correctly?

    Application.DisplayAlerts = False ' suppress overwrite warning message
    ActiveWorkbook.SaveAs Filename:="\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\313670 Avanir\Final Result\" & cellValueFolderName & "313670 Avanir Biweekly Dashboard " & cellvalueFolderName1 & ".xlsx"
    Application.DisplayAlerts = True

This code is supposed to use the first part of the path then add in a referenced cell to add an additional folder name, then add a fixed name, then grab another referenced cell to add another part of the workbook name. In this case the final path should look like this:

\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\313670 Avanir\Final Result\July-25-2018\"313670 Avanir Biweekly Dashboard July-25-2018.xlsx"

The error message is: Method 'SaveAs' of object'_Workbook' Failed

 

[Momentman]

Be sure you dont have characters like "<",">""?" in the name of the file---thats one thing to check----hopefully also, you dont have a filename which is more than 256 characters long as a result of what you have in those variables used to form the file name

 

[rplohocky]

Hello,
Fluff, I totally forgot I posted this question. I am working with other forums as well and forgot I posted it here already.

Here is more info on my issue.

Hello,
I keep getting this error when my macro hits the "saveas" point. I've spent hours trying to figure out what's wrong. Any idea's?

Sub FormatTagExpiration()
'
' This macro opens specific workbooks to "clean" them up for processing them into a summary workbook.
'
    Dim src As Workbook
    Set src = Workbooks("313670 Avanir Biweekly Dashboard Report.xlsm")
    
'Below set a CELL reference to the("313670 Avanir Biweekly Dashboard Report.xlsm") workbook.
'The numbers below refer to row number and column number respectivly. (33, 4) 33= row, 4 = column.
    Dim cellValueFolderName As String
    Dim cellValueFolderName1 As String

    cellValueFolderName = src.Worksheets("Data").Cells(168, 2) 'New folder name.
    cellValueFolderName1 = src.Worksheets("Data").Cells(168, 1) 'New folder name without the slash.

'Below goes back to "Template.xlsx" and does a saveas.
    Windows("Template.xlsx").Activate
    Application.DisplayAlerts = False ' suppress overwrite warning message
    ThisWorkbook.SaveAs Filename:="\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\313670 Avanir\Final Result\" & cellValueFolderName & "313670 Avanir Biweekly Dashboard " & cellValueFolderName1 & ".xlsx"
    Application.DisplayAlerts = True
    
End Sub

I also tried ActiveWorkbook as well, no luck.

 

[rplohocky]

Be sure you dont have characters like "<",">""?" in the name of the file---thats one thing to check----hopefully also, you dont have a filename which is more than 256 characters long as a result of what you have in those variables used to form the file name

Momentman,
I don't have any odd characters and the name is about 30 characters long. I inserted the entire macro to review.

 

[Joe4]

Make the following changes to your last section and tell us EXACTLY what the Message Box returns:
'Below goes back to "Template.xlsx" and does a saveas.

    Dim fName As String
    Windows("Template.xlsx").Activate
    Application.DisplayAlerts = False ' suppress overwrite warning message
    fName = "\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\313670 Avanir\Final Result\" & cellValueFolderName & "313670 Avanir Biweekly Dashboard " & cellValueFolderName1 & ".xlsx"
    MsgBox fName
    ThisWorkbook.SaveAs Filename:=fName
    Application.DisplayAlerts = True

 

[Joe4]

I am working with other forums as well and forgot I posted it here already.

If you are posting the same questions on other sites, this is known as "Cross-Posting".

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Most other forums have similar policies as it pertains to Cross-Posting.

 

[rplohocky]

If you are posting the same questions on other sites, this is known as "Cross-Posting".

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Most other forums have similar policies as it pertains to Cross-Posting.

Sorry, It won't happen again.

 

[Joe4]

Sorry, It won't happen again.

We are not saying you cannot do it, just if you do post the same question on other forums, please mention it and provide the links to those other threads (so, please do that now for this question).

Also, note my reply to your question up in post 5.

 

[rplohocky]

Make the following changes to your last section and tell us EXACTLY what the Message Box returns:
'Below goes back to "Template.xlsx" and does a saveas.

    Dim fName As String
    Windows("Template.xlsx").Activate
    Application.DisplayAlerts = False ' suppress overwrite warning message
    fName = "\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\313670 Avanir\Final Result\" & cellValueFolderName & "313670 Avanir Biweekly Dashboard " & cellValueFolderName1 & ".xlsx"
    MsgBox fName
    ThisWorkbook.SaveAs Filename:=fName
    Application.DisplayAlerts = True

Joe4 you are the man!
Your code told me what's happening. The issue was the macro was looking in my referenced cell and grabbing the value but because it was a date it was inserting forward slashes into the WB name, which we know can't be done. I had the cell formatted like this "mmmm-dd-yyyy" but it converted. I just made another cell reference and used "=TEXT(A168,"mmmm-dd-yyyy") and now it works. Thanks for the help!

 

[Joe4]

You are welcome.

You actually don't need an additional cell to do it on the sheet - you can do it right in the VBA code.
The VBA "FORMAT" function works the same saw as the Excel "TEXT" function, i.e.

cellValueFolderName1 = FORMAT(src.Worksheets("Data").Cells(168, 1),"[COLOR=#574123]mmmm-dd-yyyy")

[/COLOR]