Hello. I am trying to print selected worksheet in an excel workbook to .pdf. I am having trouble on line 33 of the code and could use some assistance. Thank you in advance.
Code as follows:
cXLS = AskFilename("Select Excel WorkBook",".\","Excel Files|*.xls|","*.xls",1)
DB = ObjectOpen("Excel.Application")
DB.Visible = @TRUE
DB.UserControl = @TRUE
oAPP = DB.Workbooks
oAPP.Open(cXLS)
oACT = DB.ActiveWorkBook
oSheets = oACT.Sheets
n = oSheets.Count
If n > 0
cSheets=""
hEnum = ObjectCollectionOpen(oSheets)
While 1
oSheet = ObjectCollectionNext(hEnum)
If oSheet == 0 Then Break
cSheets = StrCat( cSheets,oSheet.Name,@TAB )
ObjectClose(oSheet)
EndWhile
ObjectCollectionClose(hEnum)
ObjectClose(oSheets)
cSheet = AskItemlist("Select WorkSheet",cSheets,@TAB,@UNSORTED,@SINGLE)
; message('cSheets',cSheets)
;to go to active sheet, uncomment the following
; Specify the CSV file path
csvname = DirScript(): cSheet: '.pdf'
;message('csvname',csvname)
if FileExist( csvname ) then FileDelete( csvname )
DB.Visible = @TRUE
oWS = DB.WorkSheets(cSheet)
oWS.Activate
;message('','pause')
;ObjectClose(oWS)
oWS.SaveAs(:: Filename="%csvname%", FileFormat=%xlCSV%, CreateBackup=0)
oWS = 0
;ObjActSheet = 0
Next
;message("","")
EndIf
ObjectClose(oACT)
oAPP.Close()
ObjectClose(oAPP)
DB.Quit()
ObjectClose(DB)
Exit
One obvious problem is that you never define the variable "xlCSV". That is the kind of thing that is easy to miss when you use substitution. Also, I could be wrong but I don't recall Excel having a PDF "FileFormat" specifier.
Const xlTypePDF = 0
Member of Excel.XlFixedFormatType
VBA: ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF
@stanl: Where would i put this into the code? Thanks.
Quote from: Franchise on July 24, 2020, 03:38:33 PM
@stanl: Where would i put this into the code? Thanks.
Off the top of my head [edited]
;comment out
;oWS.SaveAs(:: Filename="%csvname%", FileFormat=%xlCSV%, CreateBackup=0)
;try
DB.ActiveWorkSheet.ExportAsFixedFormat(::Type=0,Filename="%csvname%")
MSFT documentation:
https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.exportasfixedformat (https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.exportasfixedformat)
The integer value for "xlTypePDF" that you will need to know can be found here:
https://docs.microsoft.com/en-us/office/vba/api/excel.xlfixedformattype (https://docs.microsoft.com/en-us/office/vba/api/excel.xlfixedformattype)