The main advantage of using cell references in a formula, rather than the actual data, can be seen when the data in the cells is changed. When the actual data is used for the calculation, the formula needs to be rewritten. When using cell references, the results of the formula update automatically when the data in the target cells change.
![Cell 121,2 In Excel Cell 121,2 In Excel](/uploads/1/2/5/3/125394816/890019622.png)
Hi Everyone,Background:Another department intends to ftp a.txt file from the mainframe, for meto process.The objective is to write a vb script that would be scheduled to rundaily to process this.txt file.Goal:I am working on a vba script to:a)open a text file in excel, map the text to columns, save as.xlsspreadsheetb) import excel spreadsheet to an access tableAccomplished most of (a) using the macro recorder in EXCELProblem:While the script works, my problem is:I seem to have more than 1 excel instance running. Assuming this is sobecause:a) when I go to explorer to open the.xls file that I just created, thecomputer hangs.If I exit out of access, I can then view the.xls fileb) when execute the script for the first time, I get the following errorcode, which is what I want, because EXCEL should not be already running:429ActiveX component can't create objectIf I run the script again, I get a 0, return code, which means thatexcel is running.I want to always get a 429. Getting a 0, means a previous instance ofexcel exists.' You check for an Excel instance by using GetObject.If an instance exists, you set a reference to it, but you don't release thatreference within that procedure.Then in the calling procedure you re-set that reference.I don't know for sure, but this could mess up Windows' count of how manyopen references you have to Excel.Also -you mentioned that this hidden instance of Excel doesn't show in theTask Manager.If you have a version of Windows above 98, there should be a second tab onyour Task Manager named Processes. You may well see your hidden instancethere.HTH- Turtle'mytfein' wrote in messagenews:[email protected]. Hi Everyone,Background:Another department intends to ftp a.txt file from the mainframe, for meto process.The objective is to write a vb script that would be scheduled to rundaily to process this.txt file.Goal:I am working on a vba script to:a)open a text file in excel, map the text to columns, save as.xlsspreadsheetb) import excel spreadsheet to an access tableAccomplished most of (a) using the macro recorder in EXCELProblem:While the script works, my problem is:I seem to have more than 1 excel instance running.
Assuming this is sobecause:a) when I go to explorer to open the.xls file that I just created, thecomputer hangs.If I exit out of access, I can then view the.xls fileb) when execute the script for the first time, I get the following errorcode, which is what I want, because EXCEL should not be already running:429ActiveX component can't create objectIf I run the script again, I get a 0, return code, which means thatexcel is running.I want to always get a 429. Getting a 0, means a previous instance ofexcel exists.'
![Cell Cell](/uploads/1/2/5/3/125394816/451294507.png)
BEGIN PGP SIGNED MESSAGE-Hash: SHA1Why don't you save yourself a lot of effort & use the AccessDoCmd.TransferText method? Why not just read the file with Access and skip Excel entirely?' Mytfein' wrote in messagenews:[email protected]. Hi Everyone,Background:Another department intends to ftp a.txt file from the mainframe, for meto process.The objective is to write a vb script that would be scheduled to rundaily to process this.txt file.Goal:I am working on a vba script to:a)open a text file in excel, map the text to columns, save as.xlsspreadsheetb) import excel spreadsheet to an access tableAccomplished most of (a) using the macro recorder in EXCELProblem:While the script works, my problem is:I seem to have more than 1 excel instance running. Assuming this is sobecause:a) when I go to explorer to open the.xls file that I just created, thecomputer hangs.If I exit out of access, I can then view the.xls fileb) when execute the script for the first time, I get the following errorcode, which is what I want, because EXCEL should not be already running:429ActiveX component can't create objectIf I run the script again, I get a 0, return code, which means thatexcel is running.I want to always get a 429.
Getting a 0, means a previous instance ofexcel exists.' Hi MacDermott,Thank you for responding.thx for the tip about task manager/processes -I do see excel.exe runningI do release the objExcel in another place (under CloseExcel), as theobjExcel is global.so I have a feeling it's another problem.doing research on the web.it seems that using a macro recorder is alittle dangerous as a basis for access to excel script.trying tosearch on 'opentext' to see how others have accomplished this.mytfein. Sent via Developersdex.Don't just participate in USENET.get rewarded for it! Hi MgFoster,The txt file has so many fields, that when I tried the transfer text,using the import file spec,got error msg on import that could not debug.So posted this problem on a bulletin board, and someone introduced me tothe exel opentext method, that will map out the fields.Since that idea, worked through an excel macro, decided to use thatapproach using a vba script.so now that I have the mappings via excel, maybe I'll use that to trythe import spec again using the transfer text command.mytfein. Sent via Developersdex.Don't just participate in USENET.get rewarded for it!