Converting vba to Office-JS

Question

I'm currently looking at converting some legacy vba code into Office-js, and I just wanted some advice on whether the existing code can be easily transferred, as well as whether it would work on both client and web version of Excel.

'''
Sub Notes()
Dim mySheet As Worksheet, myOtherSheet As Worksheet, myBook As Workbook 'Define your workbooks and worksheets as variables
Set myBook = Excel.ActiveWorkbook
Set mySheet = myBook.Sheets("Conversions")
Set myOtherSheet = myBook.Sheets("Additional Notes")

Dim i As Integer, j As Integer 'Define a couple integer variables for counting

j = 4 'This variable will keep track of which row we're on in Sheet2 (I'm assuming you want to start on line 28)
For i = 10 To 225 'This is the beginning the the loop which will repeat from 5 to 100 . . .
   If mySheet.Cells(i, 17).Value <> "" Then ' . . . for each digit, it will check if the cell's value is blank. If it isn't then it will . . .
      myOtherSheet.Cells(j, 2).Value = mySheet.Cells(i, 17).Value ' . . . Copy that value into the cell on Sheet2 in the row specified by our "j" variable.
      j = j + 1 'Then we add one to the "j" variable so the next time it copies, we will be on the next available row in Sheet2.
   End If
Next i 'This triggers the end of the loop and moves on to the next value of "i".
End Sub
Sub PDF_Export()
Dim ws As Worksheet
Dim strPath As String
Dim myFile As Variant
Dim strFile As String
On Error GoTo ErrHandler

Set ws = ActiveSheet

'enter name and select folder for file
' start in current workbook folder
strFile = Replace(Replace(ws.Name, " ", ""), ".", "_") _
            & "_" _
            & Format(Now(), "yyyymmdd\_hhmm") _
            & ".pdf"
strFile = ThisWorkbook.Path & "\" & strFile

myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

If myFile <> "False" Then
    ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

    MsgBox "PDF file has been created."
End If

ExitHandler:
    Exit Sub
ErrHandler:
    MsgBox "Could not create PDF file"
    Resume ExitHandler
End Sub

Sub Print_SummaryDocument()
'
' Print_Document Macro
'
    Sheets("SFS v2.1.0").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True, Collate:=True, _
        IgnorePrintAreas:=False

End Sub
Sub Print_DetailedDocument()
'
' Print_Document Macro
'
    Sheets("Detailed Statement v2.1.0").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

Sub Print_Sum()

'Below statement will print 3 copy of the Sheet1 from Page 2 to Page no: 3

Worksheets("SFS v2.1.01").PrintOut From:=1, to:=3, Copies:=1, Preview:=True

End Sub

Sub PrintRange()

Range("A1:Ag257").PrintOut , Preview:=True

End Sub

> '''

If those with better knowledge than me can confirm how easily transferable to office-js the code is, that would be brilliant (apologies for the

Lamrert

Did you have any luck in converting this code to office.js? It really sucks as far as printing is concerned. I could not figure out an replacement for the ExportAsFixedFormat method. – Dulcia

Answer 1

Yes, you will need to rewrite your code in Office.JS. you could refers to this document: https://learn.microsoft.com/en-us/office/dev/add-ins/tutorials/excel-tutorial

From your code sample, Looks like you would like to export PDF file, in office.JS you could refers to getFileAsync, you could get more information about this API usage from https://learn.microsoft.com/en-us/javascript/api/office/office.document?view=office-js#getfileasync-filetype--callback-

PS. you can also try in your Office JS code in Script Lab, https://www.myonlinetraininghub.com/script-lab-explore-the-office-javascript-api-learn-to-build-add-ins https://learn.microsoft.com/en-us/office/dev/add-ins/overview/explore-with-script-lab

Slashing

While getFileAsyc would probably work as a workaround for printing an entire document but how would you replace the ExportAsFixedFormat? I could not find any API allowing me to get the content of a single worksheet and send it to the printer. – Dulcia

Answer 2

Office Add-ins using Office JS are cross platform and will run on desktop as well as Excel Online.

You will need to rewrite your code in JavaScript. I don't know how hard it will be. It looks like your code is printing. There is no API for printing in Office JS.

Cudgel