Workbooks(FileName).Close False Takes 40 seconds to Close the Workbook. Why?

[ceddins]

Hi all,

I've created a tool in Excel that imports data from a raw data file that the user selects. When the tool has gotten the data it needs it uses this line to close the raw data file:

Workbooks(FileName).Close False

FileName is the name of the raw data file the user selected.
I've set break points in my code to find out why the entire import process takes 1 minute, and what I've discovered is that the line above is taking nearly 40 seconds by itself. I'm not saving the workbook and I have calculation set to manual, so I just can't see why it would take this line of code 40 seconds to execute. There are no events in my workbooks either.

Does anyone have any ideas or suggestions?

Thanks,
Clint

 

[SkipVought]

Hi,

I rarely use the Open, Copy, Paste, Close method of "importing" data.

Have you considered ACTUALLY importing the data via a QUERY?

For help on your current code, please post your entire procedure.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ceddins]

Hi SkipVought,

Thank you for your response. I was able to work around the problem by clearing all of the data in the workbook before closing it. I've never encountered an issue where a workbook that I was not saving took so long to close. I shaved 45 seconds off of the total run time just by inserting the code below before closing the workbook.

    'clear all data in the workbook before closing
Dim sht As Worksheet

    For Each sht In Workbooks(FileName).Worksheets
        sht.UsedRange.Clear
    Next

Also, the data that I need to import is always stored in another spreadsheet file that is exported from a web-based database my company owns. I don't have access directly to the data in the database to be able to query it. Do you mean that I should query the other spreadsheet file? Some cleanup has to be done on the other file because of the way it exports, sometimes with merged rows and strange numbers of header rows and footer rows and some of the time fields have to be converted to a usable format, and a lof of the time data spills onto a second tab if the export is large enough.

 

[SkipVought]

Yes you can query another workbook or any sheet/table in you workbook.

You issues with the table data is typical for someone who does not understand how Excel evaluates and CHANGES data that is entered into a sheet.

It would be alot better if you had a TEXT file (.csv for instance) rather than a workbook that someone else created. THAT you could IMPORT and control the data type of each column so those errors would not occur.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]