My macro batch below: Opens a list of Lotus.123 files (N9.8.0208.1200) and attempts to then save them, in turn, as Excel 97-2003 .xls via a code loop. But I keep getting the CS dialogue when opening each .123 and cant seem to bypass it (or as below, use ShellExecute to chose the default CS: Western Europe (DOS/OS2-437/US) which causes it to stall/hang ! .
Any tips on how to elegant overcome this initial CS dialogue that is halting the macro runstream ? (and causing the macro below to stall/hang) âŚideally looking for a programmatic fix ie âwithinâ Calc macro coding without recourse to Shell or terminal commands (or God-forbid âSendKeysâ !!)
Most kind
REM Import .123 Files held in external .CSV file-listing & Save as Excel 97
Sub OpenAndConvertLotus123ToExcel()
Dim sCSVFile As String
Dim sLine As String
Dim sSourceFile As String
Dim sTargetFile As String
Dim oDoc As Object
Dim FileNo As Integer
Dim i As Integer
Dim oArgs(2) As New com.sun.star.beans.PropertyValue
Dim oSaveArgs(1) As New com.sun.star.beans.PropertyValue
Dim sCommand As String
Dim oShell As Object
Dim result As Long
Dim oService As Object
' CSV file containing the list of .123 files to Convert on loop
sCSVFile = "c:\data\ws3\Calc_Convert_123_to_Excel97_LIST.CSV"
' Check if file exists
If Dir(sCSVFile) = "" Then
FlashMessage "Error: CSV file not found - " & sCSVFile, 2
Exit Sub
End If
' Open the CSV file
On Error GoTo FileOpenError
FileNo = FreeFile
Open sCSVFile For Input As FileNo
' Loop through each line in the CSV file
i = 0
Do While Not EOF(FileNo)
Line Input #FileNo, sLine
sSourceFile = Trim(sLine)
If sSourceFile <> "" Then
' Step 1: Use soffice to open the .123 file with the correct character set
sCommand = "soffice --infilter=""Text (encoded):Western Europe (DOS/OS2-437/US)"" """ & sSourceFile & """"
oShell = CreateObject("Shell.Application")
result = oShell.ShellExecute("cmd.exe", "/c " & sCommand, "", "open", 0)
' Ensure the process is started but don't wait for it to complete
If result = 0 Then
FlashMessage "Successfully opened file: " & sSourceFile, 2
Else
FlashMessage "Failed to open file: " & sSourceFile, 2
GoTo NextFile
End If
' Step 2: Load the document in LibreOffice and convert it to Excel format
oArgs(0).Name = "FilterName"
oArgs(0).Value = "Lotus"
oArgs(1).Name = "CharacterSet"
oArgs(1).Value = "Western Europe (DOS/OS2-437/US)" ' Automatically specify character set
' Try loading the document again to ensure it is opened correctly
On Error GoTo ImportError
oDoc = StarDesktop.loadComponentFromURL(ConvertToURL(sSourceFile), "_blank", 0, oArgs())
If IsNull(oDoc) Then
FlashMessage "Failed to load document: " & sSourceFile, 2
GoTo NextFile
End If
' Prepare the target filename with "_97-2003" suffix
sTargetFile = Left(sSourceFile, Len(sSourceFile) - 4) & "_97-2003.xls"
' Prepare the save arguments for Excel 97-2003
oSaveArgs(0).Name = "FilterName"
oSaveArgs(0).Value = "MS Excel 97"
' Attempt to save the document as Excel 97-2003
oDoc.storeAsURL(ConvertToURL(sTargetFile), oSaveArgs())
' Close the document
oDoc.close(True)
' Success message
FlashMessage "File converted: " & sSourceFile, 2
End If
i = i + 1
NextFile:
Loop
' Close the CSV file
Close FileNo
FlashMessage "Conversion Completed: " & i & " files processed.", 2
Exit Sub
' Error handling
FileOpenError:
FlashMessage "Error: Could not open CSV file " & sCSVFile, 2
Exit Sub
ImportError:
FlashMessage "Error: Could not process file " & sSourceFile, 2
Resume Next
End Sub
' Flash message to replace dialog interactions, lasts for 'duration' seconds
Sub FlashMessage(sMessage As String, duration As Integer)
Dim oDialog As Object
Dim oFrame As Object
Dim oWindow As Object
Dim oToolkit As Object
' Use the StarDesktop to get the current frame
oFrame = StarDesktop.ActiveFrame
' If no active frame, fallback to CurrentFrame
If IsNull(oFrame) Then
oFrame = StarDesktop.CurrentFrame
End If
' Check if oFrame is still Null
If IsNull(oFrame) Then
FlashMessage "Error: No active frame found. Unable to create message box.", 2
Exit Sub
End If
' Get the underlying window of the frame (to avoid getPeer())
oWindow = oFrame.getContainerWindow()
' Check if oWindow is valid
If IsNull(oWindow) Then
FlashMessage "Error: Could not retrieve window from frame. Unable to create message box.", 2
Exit Sub
End If
' Create the message box using the window object
oToolkit = CreateUnoService("com.sun.star.awt.Toolkit")
oDialog = oToolkit.createMessageBox(oWindow, 64, 2, "Information", sMessage)
oDialog.execute()
' Wait for the specified duration in seconds (using Timer)
Dim currentTime As Double
currentTime = Timer
While Timer - currentTime < duration
' Do nothing, just wait for the specified duration (in seconds)
Wend
' Dispose of the dialog after the duration
oDialog.dispose()
End Sub
