Unanswered: access to excel automation...seem to still have a hidden excel instance....
Another department intends to ftp a .txt file from the mainframe, for me to process.
The objective is to write a vb script that would be scheduled to run daily to process this .txt file.
I am working on a vba script to:
a)open a text file in excel, map the text to columns, save as .xls spreadsheet
b) import excel spreadsheet to an access table
Accomplished most of (a) using the macro recorder in EXCEL
While the script works, my problem is:
I seem to have more than 1 excel instance running. Assuming this is so because:
a) when I go to explorer to open the .xls file that I just created, the computer hangs....
If I exit out of access, I can then view the .xls file
b) when execute the script for the first time, I get the following error code, which is what I want, because EXCEL should not be already running:
ActiveX component can't create object
If I run the script again, I get a 0, return code, which means that excel is running.
I want to always get a 429. Getting a 0, means a previous instance of excel exists....
Function WasExcelRunningBeforeThisExecution() As Boolean
c) if I go to ctl/alt/delete/task manager, I DO NOT see any EXCEL instances running
d) checked Access HELP, for method .opentext, in EXCEL,
HELP seems to explain that the method, opens the workbook and worksheet implicitly, so I commented out my explicit EXCEL field references.
Still having trouble. Your ideas are welcome.....
The script follows below. Thank you in advance for your time....
Dim objExcel As Excel.Application
' Dim objExcelActiveWkb As Excel.Workbook
' Dim objExcelActiveWs As Excel.Worksheet
Dim blnExcelAlreadyRunning As Boolean
Public Sub EagleUpload()
Private Sub LaunchExcel()
On Error Resume Next
If WasExcelRunningBeforeThisExecution Then
blnExcelAlreadyRunning = True
Set objExcel = GetObject(, "Excel.Application")
blnExcelAlreadyRunning = False
Set objExcel = CreateObject("Excel.Application")