VBA Code for Data from a Closed Workbook

[OilEconomist]

How can I get data from a closed MS Excel 2019 Workbook into an open MS Excel 2010 Workbook? Had they both been the same version I could just import the sheet.

Thanks in advance for any suggestions as I will provide feedback. There are many posts with the same solution I'm using, but I need to have it adjusted as I never know how many rows will be in the closed workbook with the data.

For now I am using the following, but the issue becomes the number of rows as it might not always be 1000. I could make it bigger, but it's already slowing down my code as I'm running this as a loop. I'm doing this for many worksheets.

 'Obtains the JIB Data from a closed workbook

                With Sheets(Tab_Name)
                    
                       With .Range(.Cells(3, 1), .Cells(1000, 45))

                        .Formula = "='" & DirFolder & "[" & File_Name & "]Sheet1'!A3"

                        .Value = .Value

                        Sheets(Tab_Name).Cells(3, 1).Select

                    End With

                End With

I was trying to find the last row in the sheet of the closed workbook so the following in the aforementioned code:

 With .Range(.Cells(3, 1), .Cells(1000, 45))

is as follows:

 With .Range(.Cells(3, 1), .Cells(LastRow, 45))

Also, the following brings in 0s for blanks and I would like the cell to be blank if that's what it has in it. Any other suggestions?

.Value = .Value

 

[Yongle]

I am a little puzzled by your problem , but test this using a COPY of your data

    Dim hdr As Range
    Set hdr = Sheets([COLOR=#ff0000]"XXX[/COLOR]").Range("A2")
    
[COLOR=#006400]'acivate the sheet[/COLOR]
    hdr.Parent.Activate
    hdr.Offset(1).Select
[COLOR=#006400]'speed everything up[/COLOR]
    Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual
[COLOR=#006400]'get the data[/COLOR]
    With hdr.Offset(1).Resize(3000, 45)
        '.Formula = "='" & DirFolder & "[" & File_Name & "]Sheet1'!A3"
         .Formula = "=Sheet2!A3"
         .Value = .Value
[COLOR=#006400]'add a formula to count zeros in a row and set calcs to automatic[/COLOR]
        .Offset(, 45).Resize(, 1).Formula = "=COUNTIF(A3:AS3,0)"
        Application.Calculation = xlCalculationAutomatic
[COLOR=#006400]'filter the data[/COLOR]
        hdr.CurrentRegion.AutoFilter Field:=46, Criteria1:="45"
[COLOR=#006400]'delete rows that meet criteria and switch off autofilter[/COLOR]
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
        If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
    End With

 

[Yongle]

Hopefully the above has given you a workable solution

Another suggestion if you are unable to open 2019 workbook with Excel 2010
- insert ARRAY formula below in the 2019 workbook to provide the value for new variable LastRow
- amend the range in your ORIGINAL code to .Range(.Cells(3, 1), .Cells(LastRow, 45))

This ARRAY formula (in 2019 workbook) cannot be committed with {ENTER}
- must be committed with {CTRL}{SHIFT}{ENTER} - it will then look like this {=MAX(IF(A:A<>"",ROW(A:A))) }

- the formula returns the last non empty cell in column A. Place in any cell but NOT in column A

=MAX(IF(A:A<>"",ROW(A:A)))

 

[Yongle]

How can I get data from a closed MS Excel 2019 Workbook into an open MS Excel 2010 Workbook? Had they both been the same version I could just import the sheet.

Excel 2019 and Excel 2010 are not that different - so the above puzzles me slightly (unless you are using a Mac - in which case I have no idea )


Is it possible to open 2019 workbook in Excel 2010?

If you are able to open the workbook try this manually and let me know how it goes
- open both workboooks
- copy 2019 workbook data
- paste in 2 steps to 2010 workbook by pasting as normal (CTRL-V) and then paste special values only

Does that work? (if it does then it can be automated)