Using ChatGPT is only so effective. I have a blank document and I want to export data from multiple other documents, let’s say 60 documents, all into the blank document. I also want each row of new data to spit out the file name for the file that that row of data originally came from. This is the code so far.
Sub CopyDataFromODSFilesToCurrentFile()
Dim oDesktop As Object
Dim oFileAccess As Object
Dim oCurrentDocument As Object
Dim oFiles As Object
Dim oFileItem As Object
Dim oSheet As Object
Dim oCell As Object
Dim dataRange As Object
Dim consolidatedData() As String
Dim filename As String
Dim i As Integer
Dim newColumnIndex As Integer
' Get the current document
oDesktop = createUnoService("com.sun.star.frame.Desktop")
oCurrentDocument = oDesktop.getCurrentComponent()
' Specify the folder path containing the .ods files
folderPath = "FolderPath" ' Replace with your folder path
' Create a list to store data from all files
ReDim consolidatedData(0)
' Get the file access service
oFileAccess = createUnoService("com.sun.star.ucb.SimpleFileAccess")
' Check if the folder exists
If oFileAccess.exists(ConvertToURL(folderPath)) Then
' List all files in the folder
oFiles = oFileAccess.getFolderContents(ConvertToURL(folderPath), False)
' Iterate through the files
For Each oFileItem In oFiles
oFile = oFileItem.getName()
' Check if the file is an .ods file
If Right(oFile, 4) = ".ods" Then
' Correctly assign the filename
filename = Left(oFile, Len(oFile) - 4)
' Load the .ods file
oCurrentDocument = oDesktop.loadComponentFromURL("file://" & folderPath & "/" & oFile, "_blank", 0, Array())
oSheet = oCurrentDocument.Sheets(0) ' Assumes the first sheet of the loaded document
' Get the data from the active sheet
dataRange = oSheet.getDataArray()
' Create a new column with the filename
Dim filenameColumn() As String
ReDim filenameColumn(dataRange.getLength() - 1)
For i = LBound(filenameColumn) To UBound(filenameColumn)
filenameColumn(i) = "Name" & filename
Next i
' Append data with the filename
ReDim Preserve consolidatedData(UBound(consolidatedData) + dataRange.getLength())
For i = LBound(dataRange) To UBound(dataRange)
Dim rowData As String
rowData = Join(dataRange(i), " ") & " " & filenameColumn(i)
consolidatedData(UBound(consolidatedData)) = rowData
Next i
' Close the loaded document
oCurrentDocument.close(True)
End If
Next oFileItem
Else
MsgBox "Folder does not exist: " & folderPath
End If
' Get the current sheet of the current document
oSheet = oCurrentDocument.Sheets(0) ' Assumes the first sheet of the current document
' Define the column index for the new data
newColumnIndex = oSheet.getColumns().getCount()
' Insert the new data as a new column
ReDim Preserve consolidatedData(UBound(consolidatedData) + 1) ' Add one more element for headers
For i = UBound(consolidatedData) To 1 Step -1
If i = UBound(consolidatedData) Then
' Insert headers in the first row
oSheet.getCellByPosition(newColumnIndex, 0).setString("Data")
oSheet.getCellByPosition(newColumnIndex + 1, 0).setString("Filename")
Else
Dim rowDataArray() As String
rowDataArray = Split(consolidatedData(i - 1), " ")
oSheet.getCellByPosition(newColumnIndex, i).setString(rowDataArray(0))
oSheet.getCellByPosition(newColumnIndex + 1, i).setString(rowDataArray(1))
End If
Next i
' Save the current document with the copied data
oCurrentDocument.store()
oCurrentDocument.close(True)
' Show a message when the script completes
MsgBox "Script completed"
End Sub
I don’t really think ChatGPT knows what it’s doing. Does anybody in here?
EDIT: I apologize for the less than satisfactory formatting. The forum has a mind of its own.