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

0 votes
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?
Oct 27, 2022 in Others by Kithuzzz
• 38,010 points
332 views

1 answer to this question.

0 votes

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

answered Oct 27, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Why am I not being able to read the Excel sheet in my Jupyter Notebook

Verify that it is actually in your ...READ MORE

answered Mar 28, 2023 in Others by Kithuzzz
• 38,010 points
429 views
0 votes
0 answers

Data Driven Framework -- how to read and write in excel sheet using Selenium WebDriver with java

I'm using this code to read something, ...READ MORE

Oct 31, 2022 in Others by Kithuzzz
• 38,010 points
525 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
972 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,314 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
623 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
841 views
0 votes
1 answer

I want to include a pdf to excel converter in my template?

You're app hosted locally only?  But even ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,700 points
458 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,700 points
605 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP