Why can't I write out an Excel file?

I've got a VB project that cycles through a txt file and extracts data from that txt file into Excel files. The project creates Excel files based on what it finds in the txt file.

My problem: The code works beautifully on the first two sets of data it finds, but when it encounters a third set of data, although the program reads through the data, it does not create a resultant third Excel file. I've stepped through the code and confirmed that data is being encountered.

Is there a limit on the number of Excel workbooks that can be created? Here is the code snippet I have at the end of my project, after data is processed:

xls.activeworkbook.saveas Filename:=strFilename & ".xls", ReadOnlyRecommended:=False, CreateBackup:=False

xls.activeworkbook.Close

xls.workbooks.Add
xls.Visible = True


Any help is most welcome! This is driving me crazy!!!!

Maybe making the excel session visible before the save would help debug the
problem.

Could it be that strFileName is not correct or that you're waiting with a "do
you want to overwrite this file" prompt hidden?

xls.displayalerts = false
'your save line
xls.displayalerts = true

would be one way to avoid this prompt.

greaseman wrote:
>
> I've got a VB project that cycles through a txt file and extracts data
> from that txt file into Excel files. The project creates Excel files
> based on what it finds in the txt file.
>
> My problem: The code works beautifully on the first two sets of data it
> finds, but when it encounters a third set of data, although the program
> reads through the data, it does not create a resultant third Excel
> file. I've stepped through the code and confirmed that data is being
> encountered.
>
> Is there a limit on the number of Excel workbooks that can be created?
> Here is the code snippet I have at the end of my project, after data is
> processed:
>
> xls.activeworkbook.saveas Filename:=strFilename & ".xls",
> ReadOnlyRecommended:=False, CreateBackup:=False
>
> xls.activeworkbook.Close
>
> xls.workbooks.Add
> xls.Visible = True
>
> Any help is most welcome! This is driving me crazy!!!!
>
> --
> greaseman
> ------------------------------------------------------------------------
> greaseman's Profile: http://www.excelforum.com/member.php...o&userid=28808
> View this thread: http://www.excelforum.com/showthread...hreadid=535496

--

Dave Peterson

Dave,

Thanks for your reply...... the program is not waiting on a "do
you want to overwrite this file" message, and also strFileName is picked from a file selection form.....

I have no idea what is happening..... no error messages sho, and tracing through code indicates that data is being "grabbed," it's just not coming out to the Excel file.

If you have any more suggestions, please toss them at me......I'm going crazy!

Just what I mentioned before--make that instance visible to see if you can see
anything strange happening.

Good luck.

greaseman wrote:
>
> Dave,
>
> Thanks for your reply...... the program is not waiting on a "do
> you want to overwrite this file" message, and also strFileName is
> picked from a file selection form.....
>
> I have no idea what is happening..... no error messages sho, and
> tracing through code indicates that data is being "grabbed," it's just
> not coming out to the Excel file.
>
> If you have any more suggestions, please toss them at me......I'm going
> crazy!
>
> --
> greaseman
> ------------------------------------------------------------------------
> greaseman's Profile: http://www.excelforum.com/member.php...o&userid=28808
> View this thread: http://www.excelforum.com/showthread...hreadid=535496

--

Dave Peterson