Hi,
I have set up a worksheet which is set to export (print) to a PDF file. However it is a form with changing values (form values changes before each export), that's why i have put the code in a loop.
The problem is when i have to print (export) the worksheet multiple times excel gives an error;
"Run-time error '1004': Document not saved. The document may be open, or an error may have been encountered when saving."
Sometimes this happens after 30-ish export later, sometimes it never happens. The weirdest thing is when i try to debug the problem and do the steps manually no matter how many exports i take it never gives an error. I have tried setting up a time delay after each export thinking that maybe system needs a break after an export but this didn't helped either.
I need an expert eye on this code (keep in mind that i'm quite new to vba coding)
Here is the code below;
Incase you might need more context please contact me, I can provide the whole code,
I have set up a worksheet which is set to export (print) to a PDF file. However it is a form with changing values (form values changes before each export), that's why i have put the code in a loop.
The problem is when i have to print (export) the worksheet multiple times excel gives an error;
"Run-time error '1004': Document not saved. The document may be open, or an error may have been encountered when saving."
Sometimes this happens after 30-ish export later, sometimes it never happens. The weirdest thing is when i try to debug the problem and do the steps manually no matter how many exports i take it never gives an error. I have tried setting up a time delay after each export thinking that maybe system needs a break after an export but this didn't helped either.
I need an expert eye on this code (keep in mind that i'm quite new to vba coding)
Here is the code below;
Incase you might need more context please contact me, I can provide the whole code,
Code:
Dim txtID As VariantDim iRow As Integer
Dim iCount As Integer
iRow = 3
iCount = 0
Do Until IsEmpty(Worksheets("KAYIT").Cells(iRow, 34))
'set the filtered id numbers one by one
txtID = Worksheets("KAYIT").Cells(iRow, 34).Value
Worksheets("PUANTAJ").Range("H7").Value = txtID
Worksheets("PUANTAJ").Activate
'take a print
'Print
'
Dim ws As Worksheet
Dim strPath As String
Dim myFile As Variant
Dim strFile As String
Dim yil_ay As String
Dim sicil As String
On Error GoTo errHandler
'get the turkish year and month names
'get the id number from the worksheet
yil_ay = Worksheets("PUANTAJ").Range("H13").Value
sicil = Worksheets("PUANTAJ").Range("H7").Value
Set ws = ActiveSheet
'define a file name for the printout
' it will look like PUANTAJ_SICIL NO_YIL_AY
' and the directory will be the same as the workbook
strFile = Replace(ws.Name, " ", "") _
& "_" _
& sicil & "_" _
& yil_ay _
& ".pdf"
strFile = ThisWorkbook.Path & "\" & strFile
'Application.Wait (Now + TimeValue("0:00:1"))
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
iRow = iRow + 1
iCount = iCount + 1
Loop
' Enable screen refreshing.
Application.ScreenUpdating = True
MsgBox (iCount & " Records have been taken as PDF")
exitHandler:
Exit Sub
' Enable screen refreshing.
Application.ScreenUpdating = True
errHandler:
MsgBox "Encountered an error while saving!"
Resume exitHandler
' Enable screen refreshing.
Application.ScreenUpdating = True