CODEWITHSUNDEEP
vbalibreoffice
leoOrion
leoOrion

Reputation: 1957

Converting a specific sheet using sheet name to csv

Macro used:

option compatible
Sub ExportToCsv(URL as String, ParamArray sheetNames() As Variant)
  Dim saveParams(1) as New com.sun.star.beans.PropertyValue
  saveParams(0).Name = "FilterName"
  saveParams(0).Value = "Text - txt - csv (StarCalc)"
  saveParams(1).Name = "FilterOptions"
  saveParams(1).Value = "44,34,0,1,1" ' 44=comma, 34=double-quote

  GlobalScope.BasicLibraries.loadLibrary("Tools")
  URL = ConvertToURL(URL)
  document = StarDesktop.loadComponentFromUrl(URL, "_blank", 0,  Array())

  baseName = Tools.Strings.GetFileNameWithoutExtension(document.GetURL(), "/")
  directory = Tools.Strings.DirectoryNameoutofPath(document.GetURL(), "/")

  sheets = document.Sheets
  sheetCount = sheets.Count
  Dim x as Integer
  Dim requiredSheetIndex as Integer
  For x = 0 to sheetCount -1
    sheet = sheets.getByIndex(x)
    sheet.isVisible = True
    For i = LBound(sheetNames) To UBound(sheetNames)
      If StrComp(sheet.Name, sheetNames(i), vbTextCompare) = 0 Then
        requiredSheetIndex = x
      End If
    Next
  Next

  currentSheet = document.GetCurrentController.GetActiveSheet()
  sheet = sheets(requiredSheetIndex)
  document.GetCurrentController.SetActiveSheet(sheet)
  filename = directory + "/" + baseName + ".csv"
  fileURL = convertToURL(Filename)
  document.StoreToURL(fileURL, saveParams())
  document.close(True)
End Sub

I am a complete beginner in VBA, so this might be a very simple issue.

I am trying to use a macro to convert a specific sheet of a document specified as first argument to CSV. The sheet is matched based on the first match of variable args that are also passed to the macro.

Right now there is no failure but it is not picking the correct sheet. The first sheet is always being picked up.

The bug is most likely in this nested loop structure, but cant figure out what it is.

  sheets = document.Sheets
  sheetCount = sheets.Count
  Dim x as Integer
  Dim requiredSheetIndex as Integer
  For x = 0 to sheetCount -1
    sheet = sheets.getByIndex(x)
    sheet.isVisible = True
    For i = LBound(sheetNames) To UBound(sheetNames)
      If StrComp(sheet.Name, sheetNames(i), vbTextCompare) = 0 Then
        requiredSheetIndex = x
      End If
    Next
  Next

Upvotes: 0

Views: 129

Answers (1)

leoOrion
leoOrion

Reputation: 1957

The Issue was a space when passing the argument

soffice --headless "macro:///ExportToCsv.Module.ExportToCsv("path_to_file.xlsx", Data)"

I added a Trim to both the sheet name and the arguments parsed and it worked.

Upvotes: 1

Related Questions