Need Excel VBA To Create Excel 2007 Workbooks

[ByerRA]

I have an Excel 2007 .xlsm workbook with a macro that creates a workbook with a single worksheet and then imports a 150K+ lines long data file to work on.

My problem is that my macro is creating a workbook with a single worksheet in "Compatibility Mode" to import the data to and it's being cut off at the 65K limit.

How do I tell Excel 2007 VBA to create a "Excel 2007" workbook and not a "Compatibility Mode" workbook so I can import the data without it getting cut off.

Thanks in advance for any help.

 

[iliace]

Probably has to do with your default file format. Try this, at the beginning of the code:

Dim fFormat As Excel.xlFileFormat
fFormat = Application.DefaultSaveFormat
Application.DefaultSaveFormat = xlWorkbookNormal

then at the end of the code:

Application.DefaultSaveFormat = fFormat

 

[ByerRA]

Sorry for the delay, been rather busy.

Nope, that didn't change a thing. The workbook still opens in compatibility mode

Probably has to do with your default file format. Try this, at the beginning of the code:

Dim fFormat As Excel.xlFileFormat
fFormat = Application.DefaultSaveFormat
Application.DefaultSaveFormat = xlWorkbookNormal

then at the end of the code:

Application.DefaultSaveFormat = fFormat

 

[iliace]

That is the only way I was able to recreate the problem and fix it. I have no other suggestions - sorry.

 

[ByerRA]

Thanks for the help, I'll see what I can come up with

That is the only way I was able to recreate the problem and fix it. I have no other suggestions - sorry.

 

[Norie]

Why don't you specify the file format when saving the workbook(s)?

There is a FileFormat property.

 

[ByerRA]

My problem exists before I can even put data in to save.

I have data which will exceed the excel 2003 row limit so I need to create/open a excel 2007 workbook at the start to put my data in to save.

Why don't you specify the file format when saving the workbook(s)?

There is a FileFormat property.

 

[Norie]

You haven't posted any code so it's pretty hard to help.

We are basically just guessing, throwing ideas in the air etc.

Actually the first thing I would normally ask is why are you using Excel anyway when you have so many records.

I know the number of rows/columns has been increased but I really don't think it's helped things.