Hi Everyone,

Background:

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.

Goal:

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

Problem:

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:

429

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 WasExcelRunning BeforeThisExecu tion() As Boolean

On Error Resume Next

Set objExcel = GetObject(, "Excel.Applicat ion")

WasExcelRunning BeforeThisExecu tion = (Err.Number = 0) ' if err.number =

0, true else false

Debug.Print Err.Number

Debug.Print Err.Description

Err.Clear

End Function

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....

mytfein

'============== =============== ============

Option Compare Database

Option Explicit

Dim objExcel As Excel.Applicati on

' Dim objExcelActiveW kb As Excel.Workbook

' Dim objExcelActiveW s As Excel.Worksheet

Dim blnExcelAlready Running As Boolean

Public Sub EagleUpload()

LaunchExcel

ImportTextToExc el2

SaveExcelSpread sheet

CloseExcel (True)

ImportSpreadshe etToAccess

End Sub

'============== =============== ==========

Private Sub LaunchExcel()

On Error Resume Next

If WasExcelRunning BeforeThisExecu tion Then

blnExcelAlready Running = True

Set objExcel = GetObject(, "Excel.Applicat ion")

Else

blnExcelAlready Running = False

Set objExcel = CreateObject("E xcel.Applicatio n")

End If

objExcel.Visibl e = True 'False

'objExcel.Appli cation.Workbook s.Add

'Set objExcelActiveW kb = objExcel.Applic ation.ActiveWor kbook

'Set objExcelActiveW s = objExcel.Active Sheet

End Sub

'============== =============== =============

Function WasExcelRunning BeforeThisExecu tion() As Boolean

On Error Resume Next

Set objExcel = GetObject(, "Excel.Applicat ion")

WasExcelRunning BeforeThisExecu tion = (Err.Number = 0) ' if err.number =

0, true else false

Debug.Print Err.Number

Debug.Print Err.Description

Err.Clear

End Function

'============== =============== =======

Private Sub SaveExcelSpread sheet()

On Error GoTo SaveExcelSpread sheet_Err

Const cstrPath As String = "c:\EagleEhsVis its.xls"

Kill cstrPath

'Set objExcelActiveW kb = objExcel.Applic ation.ActiveWor kbook

'objExcelActive Wkb.SaveAs cstrPath

ActiveWorkbook. SaveAs Filename:=cstrP ath, FileFormat:= _

xlNormal, Password:="", WriteResPasswor d:="",

ReadOnlyRecomme nded:=False _

, CreateBackup:=F alse

SaveExcelSpread sheet_Exit:

Exit Sub

SaveExcelSpread sheet_Err:

Select Case Err.Number

Case 53 ' kill didn't find the file - ignore error

'MsgBox Err.Number & " " & Err.Description

Resume Next

Case Else

MsgBox "Error # " & Err.Number & ": " & Err.Description

Resume SaveExcelSpread sheet_Exit

End Select

End Sub

'============== =============== =====

Private Sub CloseExcel(blnH owToCloseExcel As Boolean)

On Error GoTo CloseExcel_Err

' objExcelActiveW kb.Close savechanges:=Fa lse

ActiveWorkbook. Close savechanges:=Fa lse

If Not blnExcelAlready Running Then

objExcel.Applic ation.Quit

End If

CloseExcel_Exit :

' Set objExcelActiveW s = Nothing

' Set objExcelActiveW kb = Nothing

Set objExcel = Nothing

Exit Sub

CloseExcel_Err:

MsgBox "Error # " & Err.Number & ": " & Err.Description

Resume CloseExcel_Exit

End Sub

'============== ============

'====

Sub ImportTextToExc el2()

'

ChDir "C:\"

Workbooks.OpenT ext Filename:="C:\E HSPMMt.TXT", Origin:=xlWindo ws,

StartRow _

:=1, DataType:=xlFix edWidth, FieldInfo:=Arra y(Array(0, 2),

Array(36, 2), Array _

(45, 2), Array(52, 1), Array(60, 2), Array(86, 2), Array(121,

2), Array(146, 2), Array(150, _

2), Array(152, 2), Array(161, 2), Array(163, 2), Array(174, 2),

Array(186, 2), Array(197, 2 _

), Array(207, 2), Array(208, 2), Array(209, 2), Array(210, 2),

Array(212, 2), Array(214, 2) _

, Array(221, 2), Array(222, 2), Array(230, 2), Array(240, 2),

Array(247, 2), Array(248, 2), _

Array(250, 2), Array(261, 2), Array(270, 2), Array(280, 2),

Array(290, 2), Array(297, 2), _

Array(298, 2), Array(300, 2), Array(310, 2), Array(320, 2),

Array(328, 2), Array(329, 2), _

Array(330, 2), Array(334, 2), Array(340, 2), Array(341, 2),

Array(410, 2), Array(480, 2), _

Array(481, 2), Array(499, 2), Array(519, 2), Array(520, 2),

Array(521, 2), Array(522, 2), _

Array(530, 2))

Range("A1").Sel ect

Selection.Entir eRow.Insert

Range("A1").Sel ect

ActiveCell.Form ulaR1C1 = "header"

Range("B1").Sel ect

ActiveCell.Form ulaR1C1 = "filler1"

Range("C1").Sel ect

ActiveCell.Form ulaR1C1 = "patientNum ber"

Range("D1").Sel ect

ActiveCell.Form ulaR1C1 = "filler2"

Range("E1").Sel ect

ActiveCell.Form ulaR1C1 = "PatientNam e"

Range("F1").Sel ect

ActiveCell.Form ulaR1C1 = "PatientStr eet"

Range("G1").Sel ect

ActiveCell.Form ulaR1C1 = "PatientCit y"

Range("H1").Sel ect

ActiveCell.Form ulaR1C1 = "PatientCou nty"

Range("I1").Sel ect

ActiveCell.Form ulaR1C1 = "PatientSta te"

Range("J1").Sel ect

ActiveCell.Form ulaR1C1 = "PatientZip "

Range("K1").Sel ect

ActiveCell.Form ulaR1C1 = "PatienCoun try"

Range("L1").Sel ect

ActiveCell.Form ulaR1C1 = "filler3"

Range("M1").Sel ect

ActiveCell.Form ulaR1C1 = "PatientPho ne"

Range("N1").Sel ect

ActiveCell.Form ulaR1C1 = "PatientSSn "

Range("O1").Sel ect

ActiveCell.Form ulaR1C1 = "PatientDOB "

Range("P1").Sel ect

ActiveCell.Form ulaR1C1 = "G1"

Range("Q1").Sel ect

ActiveCell.Form ulaR1C1 = "M1"

Range("R1").Sel ect

ActiveCell.Form ulaR1C1 = "filler4"

Range("S1").Sel ect

ActiveCell.Form ulaR1C1 = "R1"

Range("T1").Sel ect

ActiveCell.Form ulaR1C1 = "Rel"

Range("U1").Sel ect

ActiveCell.Form ulaR1C1 = "Chart#"

Range("V1").Sel ect

ActiveCell.Form ulaR1C1 = "E1"

Range("W1").Sel ect

ActiveCell.Form ulaR1C1 = "Medicare#"

Range("X1").Sel ect

ActiveCell.Form ulaR1C1 = "Medicaid#"

Range("Y1").Sel ect

ActiveCell.Form ulaR1C1 = "filler5"

Range("Z1").Sel ect

ActiveCell.Form ulaR1C1 = "E2"

Range("AA1").Se lect

ActiveCell.Form ulaR1C1 = "filler6"

Range("AB1").Se lect

ActiveCell.Form ulaR1C1 = "filler7"

Range("AC1").Se lect

ActiveCell.Form ulaR1C1 = "filler8"

Range("AD1").Se lect

ActiveCell.Form ulaR1C1 = "filler9"

Range("AE1").Se lect

ActiveCell.Form ulaR1C1 = "filler10"

Range("AF1").Se lect

ActiveCell.Form ulaR1C1 = "filler11"

Range("AG1").Se lect

ActiveCell.Form ulaR1C1 = "T1"

Range("AH1").Se lect

ActiveCell.Form ulaR1C1 = "filler12"

Range("AI1").Se lect

ActiveCell.Form ulaR1C1 = "filler13"

Range("AJ1").Se lect

ActiveCell.Form ulaR1C1 = "filler14"

Range("AK1").Se lect

ActiveCell.Form ulaR1C1 = "filler15"

Range("AL1").Se lect

ActiveCell.Form ulaR1C1 = "I1"

Range("AM1").Se lect

ActiveCell.Form ulaR1C1 = "filler16"

Range("AN1").Se lect

ActiveCell.Form ulaR1C1 = "filler17"

Range("AO1").Se lect

ActiveCell.Form ulaR1C1 = "filler18"

Range("AP1").Se lect

ActiveCell.Form ulaR1C1 = "U1"

Range("AQ1").Se lect

ActiveCell.Form ulaR1C1 = "filler19"

Range("AR1").Se lect

ActiveCell.Form ulaR1C1 = "filler20"

Range("AS1").Se lect

ActiveCell.Form ulaR1C1 = "U2"

Range("AT1").Se lect

ActiveCell.Form ulaR1C1 = "filler21"

Range("AU1").Se lect

ActiveCell.Form ulaR1C1 = "E3"

Range("AV1").Se lect

ActiveCell.Form ulaR1C1 = "I2"

Range("AW1").Se lect

ActiveCell.Form ulaR1C1 = "R2"

Range("AX1").Se lect

ActiveCell.Form ulaR1C1 = "A2"

Range("AY1").Se lect

ActiveCell.Form ulaR1C1 = "UDATE"

Cells.Select

Selection.Colum ns.AutoFit

End Sub

Public Sub ImportSpreadshe etToAccess()

Dim strExcelFile As String

Dim strTableName As String

Dim strSql As String

strExcelFile = "c:\EagleEhsVis its.xls"

strTableName = "T_EagleEhsVisi ts2"

strSql = "DELETE FROM " & strTableName

CurrentDb.Execu te (strSql)

DoCmd.TransferS preadsheet _

TransferType:=a cImport, _

SpreadsheetType :=8, _

TableName:=strT ableName, _

Filename:=strEx celFile, _

HasFieldNames:= True

End Sub

*** Sent via Developersdex http://www.developersdex.com ***

Don't just participate in USENET...get rewarded for it!