Overwrite existing files and save as PDF XLSM and new sheet

[Rasmusjc]

Hi

I have this save button, that saves:

• 3 sheets into pdf
• 4 sheets into a new workbook (xlsm)
• 1 sheet into a new copy inside the workbook

My problem is if i want to save the sheets again it dosn't overwrite the old one.

So my question is how to change the code so it overwrite existing files if necessary?

Here is my code

Sub Gemsom() 
 
Dim fName As String
With Worksheets("Prisliste")
    fName = ThisWorkbook.Worksheets("Prisliste").Range("D1").Value & ThisWorkbook.Worksheets("Prisliste").Range("E10").Value & ThisWorkbook.Worksheets("Prisliste").Range("D4").Value
End With
Worksheets("Tilbud").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Users\rasmu\Desktop\2019\Tilbud\" & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            
Worksheets("Lejekontrakt").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Users\rasmu\Desktop\2019\Lejekontrakt\" & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            
Worksheets("Faktura").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Users\rasmu\Desktop\2019\Faktura\" & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            
            
    Sheets(Array("Prisliste", "Tilbud", "Lejekontrakt", "Faktura")).Copy
    'there is now a new active workbook
    With ActiveWorkbook
        'save it
        .SaveAs Filename:= _
        "C:\Users\rasmu\Desktop\2019\" & ThisWorkbook.Worksheets("Prisliste").Range("D1").Value & ThisWorkbook.Worksheets("Prisliste").Range("E10").Value & ThisWorkbook.Worksheets("Prisliste").Range("D4").Value, FileFormat:=52
        'optionally close it
        .Close savechanges:=True
    End With




End Sub

 

[ZVI]

Hi,
Try the below code:

Sub GemsomPDF()
 
  Dim FileName As String, DestFolder As String, NewShName As String
  Dim Ws As Worksheet
 
  ' Set the destination folder
  DestFolder = "D:\Users\rasmus\Desktop\F" & ChrW(195) & ChrW(165) & "rvangtelt\2019\"
 
  ThisWorkbook.Activate ' It's just for the debugging
  Set Ws = Worksheets("Prisliste")
  NewShName = Ws.Range("D1").Value
  If Len(NewShName) = 0 Then
    MsgBox "D1 is empty", vbCritical, "Exit"
    Exit Sub
  End If
 
  ' Build file name of PDFs and XMLM
  FileName = Ws.Range("D1").Value & Ws.Range("E10").Value & Ws.Range("D4").Value
 
  ' Avoid blinking
  Application.ScreenUpdating = False
 
  ' 1) Copy sheet "prisliste" to a new sheet with the name in "D1"
  Ws.Copy After:=Worksheets(Sheets.Count)
  On Error Resume Next
  Application.DisplayAlerts = False
  Sheets(NewShName).Delete
  Application.DisplayAlerts = True
  Sheets(Sheets.Count).Name = NewShName
  Ws.Activate
  On Error GoTo exit_
 
  ' 2) Save 3 sheets "tilbud", "lejekontrakt", "faktura" as pdf to different folders
  Sheets("Tilbud").ExportAsFixedFormat _
                   Type:=xlTypePDF, _
                   FileName:=DestFolder & "Tilbud\" & FileName, _
                   Quality:=xlQualityStandard, _
                   IncludeDocProperties:=True, _
                   IgnorePrintAreas:=False, _
                   OpenAfterPublish:=False
  Sheets("Lejekontrakt").ExportAsFixedFormat _
                   Type:=xlTypePDF, _
                   FileName:=DestFolder & "Lejekontrakt\" & FileName, _
                   Quality:=xlQualityStandard, _
                   IncludeDocProperties:=True, _
                   IgnorePrintAreas:=False, _
                   OpenAfterPublish:=False
  Sheets("Faktura").ExportAsFixedFormat _
                   Type:=xlTypePDF, _
                   FileName:=DestFolder & "Faktura\" & FileName, _
                   Quality:=xlQualityStandard, _
                   IncludeDocProperties:=True, _
                   IgnorePrintAreas:=False, _
                   OpenAfterPublish:=False
 
  ' 3) Copy sheets "prisliste", "tilbud", "lejekontrakt", and "faktura"
  '    to a new workbook with the name in D1 & E10 & D4
  Sheets(Array("Prisliste", "Tilbud", "Lejekontrakt", "Faktura")).Copy
  'There is now a new active workbook
  With ActiveWorkbook
    'Break link
    .ChangeLink Name:=Ws.Parent.FullName, NewName:=.FullName
    'Save it
    .SaveAs FileName:=DestFolder & FileName, FileFormat:=52
    'Close it
    .Close SaveChanges:=False
  End With
 
  ' Restore initial selection of Ws
  ThisWorkbook.Activate
  Ws.Activate
 
exit_:
 
  ' Restore screen updating
  Application.ScreenUpdating = True
 
  If Err Then MsgBox Err.Description, vbCritical, "Error"
 
End Sub

Regards

 

[Rasmusjc]

Hi Vladimir

Had a little problems with the link to the folder, Rasmus -> rasmus was case sensitive but i found the error.

One thing is still missing.

The "Safe PDF" makro i use on "Prisliste" from the main workbook are not beeing copied over to the new workbook.

The reason why i need this is if change something in the "prisliste"/pricelist and need to make new pdf files i cant do it in the main workbook (Dont know how to make it). Thats why i make a copy of the sheet "pricelist" - "tilbud" - "lejekontrakt" - "faktura" then i can open the new workbook change what i need to change and push "save to pdf" and then it should overwrite the pdf files.

But im getting this error in the new created workbook "the macro in "test" could not run, it might can be found or are deactivated.. But i can se under macro that it has not been copyied.

So can i copy the macro also?

Best regards
Rasmus

 

[ZVI]

...Had a little problems with the link to the folder, Rasmus -> rasmus was case sensitive but i found the error.

My apologizing, for testing reason I used: DestFolder = "D:\Users\..." but actually it should be: DestFolder = "C:\Users\..."

The "Safe PDF" makro i use on "Prisliste" from the main workbook are not beeing copied over to the new workbook.
...
So can i copy the macro also?

The simplest way is in placing that macro in the code module of "Prisliste" sheet, rather than in the standard code Module1 or elsewhere.

 

[ZVI]

The reason why i need this is if change something in the "prisliste"/pricelist and need to make new pdf files i cant do it in the main workbook (Dont know how to make it)

No need to copy these sheets into a separate workbook.
The below part of the code saves 4 sheets of the main workbook into the single PDF file:

  ' === Create PDF file of 4 sheets in the main workbook ===
 
  ' Select 4 sheets to export it as PDF (single file)
  Sheets(Array("Prisliste", "Tilbud", "Lejekontrakt", "Faktura")).Select
  ' Export as PDF
  ActiveSheet.ExportAsFixedFormat _
                   Type:=xlTypePDF, _
                   FileName:=DestFolder & "MyFolder\" & FileName, _
                   Quality:=xlQualityStandard, _
                   IncludeDocProperties:=True, _
                   IgnorePrintAreas:=False, _
                   OpenAfterPublish:=False
  ' Deselect sheets, restore original selection
  Sheets("Prisliste").Select

Just change the destination "MyFolder" as required and insert that code into GemsomPDF macro

 

[Rasmusjc]

It's a bit difficult for me to explain how the whole calender works. But i will give it a try.

I have a "pricelist" where i put in data like Name - adress - number - and what the customer wants to rent (tents - chairs - floor ect.)

Then in "tilbud"/offer - "lejekontrakt"/lease - "faktura"/invoice it takes the info from pricelist and place it in here.
Its because im sending out these 3 pdf files to the customer.

In pricelist i write the number of the invoice ind D1. When i push save it saves the 3 sheets as a pdf and then i copies the "prisliste"/pricelist to a new sheet with the number of the invoice in D1.

Lets say the number is 1, then the new sheet in the work book is named 1 and have the exact same data as in pricelist.

Then i go to the calender find the day where the costumer need the tent and i this spot i write 1. then it takes the data from sheet 1 and places it in the calender.

When a new costumer ask for a tent i change the number in "pricelist"D1 to 2, save to pdf - a sheet "2" is created - i type 2 in the calender ect.

The problem now is if the customer with the first invoice wants to change the order and i go into sheet "1" and change it. Then i need to make new pdf files. So i click the safe to pdf, but then it takes the info from the "pricelist" - "offer" - "lease" - "invoice" and safe that data, not the data i have changed i sheet "1".

The reason why i thought it would be smart to make a new workbook with the name from "pricelist" and the other 3 sheets was, if i needed to change something i could do it here and just push save. But i cant, it re-open the "calender"-workbook and uses the "pricelist" from this workbook.

The reason why i made it this way was to avoid typing the same thing many places as i did before.

 

[Rasmusjc]

Hi ZVI
The Code has been working well since 2019.
I would like to made a upgrade, dont know if you can help me out?

Is it possible to change the "save as pdf"-code when i use it on "pricelist"?

The code makes a copy of the sheet inside the workbook and name it (D1).
Then i got "Pricelist" and "1"

Thats fine. But i would like it to change the "save as pdf" in the new sheet "1" so it changes the references.
Dim fName1 As String
With Worksheets("pricelist")
fName1 = ThisWorkbook.Worksheets("pricelist").Range("D1").Value & ThisWorkbook.Worksheets("pricelist").Range("E10").Value & ThisWorkbook.Worksheets("pricelist").Range("D4").Value & ThisWorkbook.Worksheets("pricelist").Range("E10").Value & ThisWorkbook.Worksheets("Tilbud").Range("A9").Value
End With
So the ("pricelist") changes to the name in (D1) (in this example "1")

Dim fName1 As String
With Worksheets("1")
fName1 = ThisWorkbook.Worksheets("1").Range("D1").Value & ThisWorkbook.Worksheets("1").Range("E10").Value & ThisWorkbook.Worksheets("1").Range("D4").Value & ThisWorkbook.Worksheets("1").Range("E10").Value & ThisWorkbook.Worksheets("Tilbud").Range("A9").Value
End With

Its because if I change something in the order it saves the data in "Pricelist" and not in the sheet "1"

Its a bit difficult to explaine but i hope it makes sence.