CODEWITHSUNDEEP
excelvbaoffice-js
David Byfield
David Byfield

Reputation: 81

Converting VBA to Office.js

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 the desktop 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.

Upvotes: 7

Views: 8987

Answers (2)

Raymond Lu
Raymond Lu

Reputation: 2236

Yes, you will need to rewrite your code in Office.js. You could refer to this document: Tutorial: Create an Excel task pane add-in.

From your code sample, it looks like you would like to export a PDF file in Office.js, but you could refer to getFileAsync. For more information on the API usage refer to Office.Document interface.

P.S. You can also try in your Office.js code in Script Lab:

[1] Script Lab – Explore the Office JavaScript API & Learn to Build Add-Ins

[2] Explore Office JavaScript API using Script Lab

Upvotes: 7

Rick Kirkham
Rick Kirkham

Reputation: 9659

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.

Upvotes: 2

Related Questions