473,703 Members | 2,307 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

access to excel automation..pro blem: seem to have a hidden instance of excel

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!
Nov 13 '05 #1
8 3368
You check for an Excel instance by using GetObject.
If an instance exists, you set a reference to it, but you don't release that
reference 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 many
open references you have to Excel.

Also -
you mentioned that this hidden instance of Excel doesn't show in the
Task Manager.
If you have a version of Windows above 98, there should be a second tab on
your Task Manager named Processes. You may well see your hidden instance
there.

HTH
- Turtle

"mytfein" <an*******@devd ex.com> wrote in message
news:41******** **************@ news.newsgroups .ws...
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!

Nov 13 '05 #2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why don't you save yourself a lot of effort & use the Access
DoCmd.TransferT ext method? It reads the text file directly into Access.
Use an Import/Export specification to indicate the column names & data
types of the text file data.

Read the Access help article on TransfetText Method.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQUD4tYechKq OuFEgEQIbwgCgyC au4TfVEm1MHnU0m gTDS/CV9aUAnjR6
vdKhOqxJalgbB4y WB3gTbDIO
=pxyk
-----END PGP SIGNATURE-----
mytfein wrote:
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


Nov 13 '05 #3
I've had this exact same problem before. I solved it by eliminating
"implicit references". Check out the Google Groups archives for more
detail about this.

This line looks troublesome to me:
ActiveWorkbook .SaveAs <blah-blah-blah>


It looks like "ActiveWorkbook " implicitly references an Excel object
(I don't remember which one and can't check cuz I fried the PC with
Office on it).
Nov 13 '05 #4
Why not just read the file with Access and skip Excel entirely?

"mytfein" <an*******@devd ex.com> wrote in message
news:41******** **************@ news.newsgroups .ws...
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!

Nov 13 '05 #5
Hi MacDermott,

Thank you for responding.....

thx for the tip about task manager/processes --
I do see excel.exe running
I do release the objExcel in another place (under CloseExcel), as the
objExcel is global.....

so I have a feeling it's another problem....

doing research on the web...it seems that using a macro recorder is a
little dangerous as a basis for access to excel script.....tryi ng to
search on 'opentext' to see how others have accomplished this....

mytfein

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #6

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 to
the exel opentext method, that will map out the fields....

Since that idea, worked through an excel macro, decided to use that
approach using a vba script....

so now that I have the mappings via excel, maybe I'll use that to try
the import spec again using the transfer text command....

mytfein
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #7

Hi Mathew,

I think you have a point....

Someone suggested that using the excel macro recorder as a basis for
writing a vba script is a little dangerous...
because of implicit references...

so I'm surfing the web for 'opentext' to see how someone would have done
the script, properly opening excel with the
objects that it needs...

mytfein
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #8

Mathew,

How do you go to Google groups archives to find the info...
what keywords do you suggest I search on?

Do you mean something like this?
http://groups.google.com/groups?num=...&oe=UTF-8&q=ac
cess+to+word+au tomation+mailin g+labels

thx,
mytfein


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1820
by: Darren Barrick via .NET 247 | last post by:
Hello All! I am using Office Automation in one of my applications in orderto read/write to an excel spreadsheet from a VB.Net application. I thus create the application with ExcelApplication = New Excel.Application() I wish to keep my application hidden, and thus I use
10
5588
by: Mark Day | last post by:
Hi All, I am using Access 2000 to generate over 40 Excel charts and pivot tables using early binding to the Excel 9.0 object library. I am finding that if I show the Excel object while processing the charts, the whole process completes in around 2 minutes. However if I hide the Excel object the same process takes around 27 minutes to complete. I have played around with screen updating and this makes no real difference to the time. Does...
6
3708
by: Ecohouse | last post by:
I have a computer with XP on it. I loaded Office 97 first because I needed Access 97 for some work. I then loaded Office 2000. Everything seemed to be running fine. But I have come across a few problems. 1) I have hyperlinks in some tables and when I try to access the hyperlinks I get an error message "An unexpected error has occurred. 2) I've been trying to do some automation through Access for MS Word using VBA code. I've been...
9
2327
by: Tony Williams | last post by:
I have an Access database that we use as a document index system. The documents can be Word, Excel, pdf's etc I have a command button on a form that opens the document in whatever program is relevant. The code I use is Private Sub Cmdstart_Click() On Error GoTo Err_Handler Dim strPath As String If IsNull(DocURLtxt.Value) Then strmsg = "You must enter the Document URL to use this function
8
8087
by: Colleyville Alan | last post by:
I have been working on an Access app that takes info from a file and writes it to a spreadsheet on a form, simultaneously saving the spreadsheet to Excel. I got the idea that the same concept could work in reverse, i.e. we have a cost model written in Excel that calculates the profitability of customer accounts based on several inputs and they need to be updated at least once per year. These cost models sit on lots of people's hard...
1
2206
by: Gary Cobden | last post by:
I have a problem with the following code, which leaves an instance of Excel visible in Task Manager. By a process of elimination I have got it down to the fact that something in the DoCmd.Transfer Spreadsheet line is holding on to an Excel reference somewhere - can anybody assist (if I comment this line out, the instance is released, and not visible in Task Manager). Private Sub btnLoadData_Click()Dim xlApp2 As ObjectDim xlBook As...
7
5338
by: taylor.bryant | last post by:
I am running: Win XP SP2 Excel 2002, Access 2002 (Office XP SP3) Using Visual Basic (not VB.NET) At one point (prior to XP SP2?!? - I can't pin it down), this did not happen and I was easily able to destroy instances of excel (with the exact same code). I have read many, many posts, and they seem to get bogged down in specifics. So I cribbed this program from the automation help file, simplified it further, so hopefully someone can
6
12501
by: Matthew Wieder | last post by:
I have the following requirements: Build a stand-alone C# application that asks the user to click in a cell in an Excel spreadsheet, and then displays the address of that cell in the C# application. It seems simple enough, but the problem I'm encountering is as follows: In order for the user to select the cell from Excel, they must first click once on the Excel window to give it focus and then their second click is what changes the cell...
12
3220
by: elziko | last post by:
I'm using late binding (I must) to automate Excel. My code opens Excel after createing and poulating some sheets. My problem is that when the user finally decides to close Excel its process is left running until my application closes. I have tried setting my Excel.Application object to Nothing. I have tried to then fore the GC into action using:
0
9254
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9017
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8967
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7872
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6592
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4687
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3125
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2458
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2070
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.