I want to make Excel read a value in Calc and copy it to my sheet in Excel

Question

Each of the more than 500 Calc (.ods) files I have contains some values that I wish to reference or copy to an Excel file. I want my Excel file to be updated as soon as I open it because those Calc files are constantly updated. How do I go about doing it?

Answer 1

Here is the sample code that will allow you to process each file. Please read the comments in the code for details.

'Allows you to execute code when the workbook first opens.
Private Sub Workbook_Open()
    GetFiles
End Sub

'Subroutine that actually processes the files. This subroutine makes a list of each spreadsheet's filename along with all of its worksheets. A conditional statement is also included to display how copying and pasting of cell range can be achieved.
Public Sub GetFiles()

    'Create all variables used in this subroutine
    Dim directory As String, fileName As String, currentWorkbook As Workbook, sheet As Worksheet, i As Integer, j As Integer

    'Stop screen updating, events, and alerts until this subroutine has finished execution.
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False

    'Pull the directory and file extension data from the "Config Data" worksheet.
    directory = Sheets("Config Data").Cells(1, 2)
    fileExt = Sheets("Config Data").Cells(2, 2)

    'Find the first file in the specified directory with the specified file extension.
    ' If the Dir function finds multiple files matching the file extention, the function can be called again without any arguments to return the next file.
    fileName = Dir(directory & "*." & fileExt)

    ' Start a loop control structure to process each file.
    Do While fileName <> ""
        i = i + 1
        j = 2

        ' Store filename of each spreadsheet in its own row
        Sheets("Sheet1").Cells(i, 1) = fileName
        Set currentWorkbook = Workbooks.Open(fileName:=directory & fileName)

        If fileName = "StockChart.ods" Then
            'Copying a range from the current workbook that is being processed
            currentWorkbook.Sheets("Sheet1").Range("B3:G21").Copy
            'Pasting a range to the current workbook that is running the macro
            Sheets("Sheet1").Range("F11").PasteSpecial
        End If

        ' For each worksheet in the current workbook, the worksheet name is being stored in the column next to its workbook filename.
        For Each sheet In currentWorkbook.Worksheets
            Sheets("Sheet1").Cells(i, j) = sheet.Name
            j = j + 1
        Next sheet

        ' Close current workbook
        currentWorkbook.Close

        ' Check if more files exist that match the file extension provided.
        fileName = Dir()
    Loop

    'Enable screen updating, events, and alerts
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True

End Sub