-2

I need a way to translate a VBA Excel macro to Google sheets, the code below transfers a list of data to an invoice and then saves it as a PDF. I searched online for a translator but didn't find it. If some one knows one or if some one can translate this one.

Private Sub CommandButton1_Click()

    'define value
    Dim customer As String
    Dim invoicenumber As Long
    Dim invoicedate As Long
    Dim path As String
    Dim myfilename As String
    Dim rate As Long
    Dim r As Long

    'define our last row
    lastrow = Sheets("Dispatch1").Range("O" & Rows.Count).End(xlUp).Row

    ' start at row 5
    r = 5
    For r = 5 To 10
        If Cells(r, 1).Value = "done" Then GoTo nextrow
        invoicedate = Sheets("Dispatch1").Cells(r, 5).Value
        rate = Sheets("Dispatch1").Cells(r, 15).Value
        invoicenumber = Sheets("Dispatch1").Cells(r, 17).Value
        Amount = Sheets("Dispatch1").Cells(r, 19).Value
        Application.DisplayAlerts = False
        Sheets("invoiceblank").Select

        ' map the variables to invoice worksheet data
        ActiveSheet.Range("F4").Value = invoicedate
        ActiveSheet.Range("G4").Value = invoicenumber
        ActiveSheet.Range("F18").Value = rate
        path = "C:\Users\Dell\Desktop\feb" 
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        fileName:="C:\Users\Dell\Desktop\aa new trucking\feb\" & _
        ActiveSheet.Range("G4 ").Value & _
        Worksheets("Customers").Range("A1").Value & _
        ActiveSheet.Range("A16").Value & ".pdf", _
        OpenAfterPublish:=False

        'our label next row. Labels have a colon after their name

        nextrow:

    Next r

End Sub
VLAZ
  • 26,331
  • 9
  • 49
  • 67
dmitriy motly
  • 3
  • 1
  • 2
  • 1
    excel is VBA and google is Java, two different languages. They are not comparable. – Davesexcel Jan 31 '20 at 05:30
  • Does google sheets run vba macros? If not yhen you need to write it in the google sheets equivalent - good news is you have a head start: the logic or basic structure exists already. – Solar Mike Jan 31 '20 at 05:30
  • There are several questions about this on here, read them. This is but one and others may be more relevant: https://stackoverflow.com/q/46648022/4961700 – Solar Mike Jan 31 '20 at 05:33
  • 2
    Short answer: There exists no translator and you will have to translate it manually on your own. • *"… or if some one can translate this one"* note that Stack Overflow is no free translating/coding service. Therefore you have to start on your own. If you get stuck or errors you can ask a question to the specific point you got stuck. – Pᴇʜ Jan 31 '20 at 06:58

1 Answers1

0

You can try the following code:

function exportSingleSheetToPdf() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dispatch1 = ss.getSheetByName("Dispatch1");
  var invoiceblank = ss.getSheetByName("invoiceblank");
  var customers = ss.getSheetByName("Customers");

  var lastrow = getLastRowSpecial(ss.getRange("O:O").getValues());

  for (var r = 5;r<11;r++) {
    if (dispatch1.getRange(r, 1).getValue() == "done") {continue;}
    var invoicedate = dispatch1.getRange(r, 5).getValue();
    var rate = dispatch1.getRange(r, 15).getValue();
    var invoicenumber = dispatch1.getRange(r, 17).getValue();
    var amount = dispatch1.getRange(r, 19).getValue();

    invoiceblank.getRange("F4").setValue(invoicedate);
    invoiceblank.getRange("G4").setValue(invoicenumber);
    invoiceblank.getRange("F18").setValue(rate);

    var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export");
    var projectname = SpreadsheetApp.getActiveSpreadsheet();
    invoiceblank.copyTo(newSpreadsheet);

    newSpreadsheet.getSheetByName('Sheet1').activate();
    newSpreadsheet.deleteActiveSheet();

    var pdf = DriveApp.getFileById(newSpreadsheet.getId()).getAs('application/pdf').getBytes();

    var folder = DriveApp.getFolderById(""); // Fill in the id of the folder in drive you want your pdf to be saved in.
    var fileName = invoiceblank.getRange("G4").getValue() + customers.getRange("A1").getValue() + invoiceblank.getRange("A16").getValue() + ".pdf";
    var file = folder.createFile(fileName, pdf);

    DriveApp.getFileById(newSpreadsheet.getId()).setTrashed(true);
  }
}

function getLastRowSpecial(range){
  var rowNum = 0;
  var blank = false;
  for(var row = 0; row < range.length; row++){

    if(range[row][0] === "" && !blank){
      rowNum = row;
      blank = true;
    }else if(range[row][0] !== ""){
      blank = false;
    };
  };
  return rowNum;
}

Since this is google sheets, your pdf will be saved to your google drive.

Make sure you fill in the folder id of the folder you want your files in. It is indicated in the code where you should do that.

Good luck :)

user11221377
  • 125
  • 9