Import from excel workbook with more than 65536 rows

[willwall]

I have been using power query to import data from an excel workbook which has worked great. The problem is that now the data on the excel workbook i am importing from has gone over to 2 sheets (maxed out rows). Is there a way to get query to import the data from 2 sheets?

 

[Joe4]

Which version of Exel are you using? I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).

All the recent Excel versions (since 2003, I believe) that use the "xlsx", "xlsm", or "xlsb" formats allow for over 1 million rows of data on an Excel sheet.

 

[willwall]

Hello Joe4..
I have updated my account details now. I am using office 365 version 2110.

 

[Joe4]

Is this Excel file being generated outside of Excel, from another program, perhaps?
Perhaps it is exporting it in the old "xls" Excel format.

If you do a "Save As", and save it as the Excel option that shows the "xlsx" extension, it should increase your total rows.

 

[willwall]

Yes it is being generated by another program. I saved it to "xlsx" and it still wants to go over to sheet 2 despite the fact that it hasn't reached 1 mil rows on sheet one?

 

[Joe4]

Yes it is being generated by another program. I saved it to "xlsx" and it still wants to go over to sheet 2 despite the fact that it hasn't reached 1 mil rows on sheet one?

View attachment 51644

What still wants to go over to sheet 2?
If you change the format of the Excel sheet after the data has been generated, and the program has already spit out to two sheets, changing the format at that time (after the fact) is too late.
If the program you are exporting the Excel file from is itself spitting it out to two sheets, do you have the ability to export it to the "xlsx" format instead of the "xls" format?

If not, then I would recommend fixing it after the fact, i.e. either manually updating it or using VBA to:
- changing the format from "xls" to "xlsx" to get the additional rows
- copy the data from Sheet2 to the bottom of Sheet1

Assuming that there are less than 1 million rows, you would then have all the data on Sheet1 and could proceed with your Power Query task.

 

[willwall]

The program that I use to export the excel file is splitting it into 2 sheets. Looks like the best option is manually copying sheet 2 over to sheet one. Is there a vba code for this? I could also do it manually I suppose.

 

[Joe4]

This code would copy the data and delete sheet 2.

Sub MyCopyData()
    Sheets("Sheet2").Range("A1").CurrentRegion.Copy Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Application.DisplayAlerts = False
    Sheets("Sheet2").Delete
    Application.DisplayAlerts = True
End Sub