Alternsti
02-12-2022, 05:02 PM
Hi everyone,
Right now I'm able to generate xlsx/pdf files based on a worksheet.
1- User must fill the worksheet 'Names"
2- User activate the macro and user must select which excel template to use to fill new xlsx files. Template have all the informatios to build several xlsx files but we only need to generate few sometime.
Once completed, every xlsx file have 2 worksheets ("DS" & "Data"). I want to replace only cell formulas using ex. "*Data*" by the cell values & then delete the "Data" worksheet. I've tried several time to figure out but I'm stuck. I think I'll need to use another macro to find&replace into specific folder.
Sub GF()
'Pick the template to copy
PT = Application.GetOpenFilename("Excel Files,*.xlsx,", , "Select the template", False)
'Get filenames for another workbook/worksheet
NFCLN = "GF_WIP.xlsm"
Workbooks(NFCLN).Worksheets("Names").Activate
PDE = Cells(Rows.Count, 1).End(xlUp).Row
'Disable screen flickering.
Application.ScreenUpdating = False
'Loop
Dim i As Integer
For i = 2 To PDE
'Get filename
NI = Workbooks(NFCLN).Worksheets("Names").Cells(i, 1)
NF = "xxxx-xxxxx-" & NI & ".xlsx"
NI_PDF = Workbooks(NFCLN).Worksheets("Names").Cells(i, 2)
'Create the file
PD = "Z:\x\x\x\x\x\x\Excel\" & NF
Dim oFSO As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Call oFSO.CopyFile(PT, PD)
'Open the file
Workbooks.Open Filename:=PD
'Save XLSX file
'The following did not work
ActiveWorkbook.SaveAs Filename:=NomFichier, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
'Generate pdf file
PTPDF = "x:\x\x\x\x\x\x\PDF\" & "xxx-xxxxxx-" & NI_PDF & ".pdf"
Workbooks(NomFichier).Worksheets(1).ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=PTPDF, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False
'Close Excel file
Workbooks(NF).Close
'Go for the next one
Next i
'Enable the screen.
Application.ScreenUpdating = True
MsgBox "Done"
End Sub
Right now I'm able to generate xlsx/pdf files based on a worksheet.
1- User must fill the worksheet 'Names"
2- User activate the macro and user must select which excel template to use to fill new xlsx files. Template have all the informatios to build several xlsx files but we only need to generate few sometime.
Once completed, every xlsx file have 2 worksheets ("DS" & "Data"). I want to replace only cell formulas using ex. "*Data*" by the cell values & then delete the "Data" worksheet. I've tried several time to figure out but I'm stuck. I think I'll need to use another macro to find&replace into specific folder.
Sub GF()
'Pick the template to copy
PT = Application.GetOpenFilename("Excel Files,*.xlsx,", , "Select the template", False)
'Get filenames for another workbook/worksheet
NFCLN = "GF_WIP.xlsm"
Workbooks(NFCLN).Worksheets("Names").Activate
PDE = Cells(Rows.Count, 1).End(xlUp).Row
'Disable screen flickering.
Application.ScreenUpdating = False
'Loop
Dim i As Integer
For i = 2 To PDE
'Get filename
NI = Workbooks(NFCLN).Worksheets("Names").Cells(i, 1)
NF = "xxxx-xxxxx-" & NI & ".xlsx"
NI_PDF = Workbooks(NFCLN).Worksheets("Names").Cells(i, 2)
'Create the file
PD = "Z:\x\x\x\x\x\x\Excel\" & NF
Dim oFSO As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Call oFSO.CopyFile(PT, PD)
'Open the file
Workbooks.Open Filename:=PD
'Save XLSX file
'The following did not work
ActiveWorkbook.SaveAs Filename:=NomFichier, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
'Generate pdf file
PTPDF = "x:\x\x\x\x\x\x\PDF\" & "xxx-xxxxxx-" & NI_PDF & ".pdf"
Workbooks(NomFichier).Worksheets(1).ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=PTPDF, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False
'Close Excel file
Workbooks(NF).Close
'Go for the next one
Next i
'Enable the screen.
Application.ScreenUpdating = True
MsgBox "Done"
End Sub