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 in Others by Kithuzzz
• 20,660 points
39 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 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
0 answers
0 votes
1 answer

I have filtered my Excel data and now I want to number the rows. How do I do that?

Solution Filter your data. Select the cells you want ...READ MORE

answered Nov 14 in Others by narikkadan
• 37,660 points
29 views
0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25 in Others by narikkadan
• 37,660 points
19 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,720 points
451 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
2,218 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 in Others by gaurav
• 22,040 points
69 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 in Others by Edureka
• 13,640 points
205 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 in Others by narikkadan
• 37,660 points
49 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 in Others by narikkadan
• 37,660 points
62 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