sometimes excel crashing...when try to run a macro!!!

[Premanshu]

Hi All,

I have written a macro program which will pull info/data from a text file and save it into an excel sheet. Once this process is done it will run another procedure which will read through the imported data and create shapes (i.e rectangles, connectors, operator shapes...etc.) on the active sheet and also name them. This seems to be working fine but the only challenge i am facing is once everything is done i get a error -popup saying "Microsoft Excel has encountered an error and needs to be closed, we are sorry for the incovinience" with a checkbox option to recover my work. Also i noticed if i pause the macro and then continue it step by step using F8 it won't crash.

Could anyone please advice why am i getting this error and how to get rid of it.

Also when i try to run the macro to create shapes with the info which is already stored in the excel sheet it does works very well and does not gives error. It's only when i try running the macro with option to pull it from the text file after processing all the steps mentioned in the macro excel crashes.

Any help/advice would be of great help for me. Also i was thinking if there's a way i can release some memory excel is using...

Regards,
Premanshu

 

[SMB Cooper]

Please try this link (Applies to Excel 2007 & Excel 2010)....


Excel: How to troubleshoot crashing and not responding issues in an Excel Files

 

[Michael M]

You may also need to import ALL the data first AND then run the shapes code.
Bit hard to make assumptions without seeing the code or getting a better idea of what you are actually doing

 

[Premanshu]

Hi SMB Cooper, Michael M,

first of all thank you very much for the response, the link shared was very informative and i have saved it for my future reference however it did not help me for now. I also could not paste the whole code here as that is on a encrypted office system and nothing can taken from there. I can't even open mrexcel.com fom that system. but let me type a sample code here for your reference.

dataFile = application.getopenfilename
workbooks.opentext filename:=datafile, origin:=xlwindows, startrow:=1, datatype:=xldelimited, textqualifier:=xldoublequote, consecutivedelimiter:=false, tab:=false, semicolon:=false, comma:=true, space:=false, other:=false, fieldinfo:=array(array(1,1),array(2,1),..........array(22,1)),trailingminusnumbers:=true

activeworkbook.rows("2:1000").copy
modalworkbook.activate
sheets("sheet3").select
range("a3").pastespecial xlpastevalues
workbooks(datafile).close

call create_shapes

one more thing which i have now noticed is if i open this macro file and run this macro while i have another very heavy excel file open at the back then it does not crashes and everything works fine. But if i close the other heavy excel file and then try running this code it will run fine and then again at the end after doing everything excle will crash.

Please see if you have anymore ideas with these info.

Regards,
Premanshu