Automate Print to pdf with a loop getting info from cells

**MigueX** · 03-11-2021, 06:19 AM

Hey all

So i always use a macro to Cut and zip some files with password, but now instead of 10 files they will be 120!

How can i put this code in a loop and get the info from cells?

Please Login or Register  to view this content.

Tx in advance!

**Joske920** · 03-11-2021, 08:41 AM

Hi,

We will need more information.
the code goes to only 1 sheet
do you have a workbook with 120 sheets?
and what information do you want from what cells

**MigueX** · 03-11-2021, 02:12 PM

Hey Joske920,

i have 1 sheet for each file, so i have 120 sheets.

Sheets("Sheet1").Select -> get on cell A2
Filename:= _ "C:\Saves\File1.pdf"-> get on cell B2

Sheets("Sheet1").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Saves\File1.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False

strSourceFileName is the same as above but in .zip -> get on cell C2
strDestFileName is the same as above -> get on cell B2
strPassword -> get on cell D2

strDestFileName = "C:\Saves\Detalhe.zip"
strSourceFileName = "C:\Saves\Detalhe.pdf"
str7ZipPath = "C:\Program Files\7-Zip\7z.exe"
strPassword = "Pass"

strCommand = str7ZipPath & " -p" & strPassword & " a -tzip """ & strDestFileName & """ """ & strSourceFileName & """"
Shell strCommand

Tx!

**Joske920** · 03-11-2021, 03:17 PM

Hi,

Give this a try
Put it in a module

Please Login or Register  to view this content.

in case you want to add a page with a button to run this subroutine you need to remove the ' infront then comment lines and change the name extrasheet to the name of that 'menu' sheet