J
JingleRock
EXCEL 2003 VBA:
I have several Macro Projects, using .xlA files, stored on my local
hard drive; each has a 'Public Sub Autpen' stmt. Each .xlA file is
fired by a Windows XP 'Scheduled Task' that includes Scheduled Times
when my machine is "logged off" from our network. These macros, when
running in a "logged off" mode, have read and write access to the same
network drives that I have permissions for when my machine is "logged
on". (The .xlA files are NOT set-up as 'Add-Ins' in EXCEL.) These
macros are working as intended.
Now, I need to complete a similar macro that has a 'copy/paste from
WORD' procedure. I have been able to do this, using both late and
early binding, when my machine is "logged-on" to our network. However,
I also need to do this when my machine is "logged-off"; neither early
nor late binding is working -- the primary data file becomes "locked
for editing" by the network. (The VBA code is in a .xlA file stored on
my hard drive; a Scheduled Task opens this file at a specified time
each day.) The Scheduled Task fires, but becomes locked in "Running"
mode. We are running Office 2003 and Windows XP Pro.
Below is my early binding code procedure:
Public Sub Import_From_WORD_Tables()
Dim objWord As Word.Application
Dim objDoc As Word.Document
Dim wordvalue As Variant
Dim j As Integer
Dim lngErrNo As Long
Dim strErrSrc As String
Dim strErrDesc As String
On Error GoTo PROC_ERR
Application.DisplayAlerts = False
PrimaryDataWB.Activate '<<== this is primary data file
Set objWord = New Word.Application
objWord.Visible = False
Set objDoc = Documents.Open(myPathPF_BATCH & "T and A.rtf")
objDoc.Activate
'For "Rows" 1 - 8 in Table 1
For j = 1 To 8
wordvalue = objDoc.Tables(1).Columns(4).Cells(j + 2)
ActiveWorkbook.Sheets(3).Cells(j + 17, 11) =
Application.WorksheetFunction.Clean(wordvalue)
Next j
objDoc.Close
objWord.Quit
Set objDoc = Nothing
Set objWord = Nothing
Exit Sub
PROC_ERR:
lngErrNo = Err.Number
strErrSrc = "->ADJUSTMENT_TEST()->" & Err.Source
strErrDesc = Err.Description
'Disable error handling
On Error GoTo 0
Err.Raise lngErrNo, strErrSrc, strErrDesc
End Sub
I have been able to determine that the macro crashes at the 'Set
objWord = ...' statement above.
Since I am running this logged-off, the above error trapping is doing
nothing for me. Any ideas?
TIA, I appreciate your help.
I have several Macro Projects, using .xlA files, stored on my local
hard drive; each has a 'Public Sub Autpen' stmt. Each .xlA file is
fired by a Windows XP 'Scheduled Task' that includes Scheduled Times
when my machine is "logged off" from our network. These macros, when
running in a "logged off" mode, have read and write access to the same
network drives that I have permissions for when my machine is "logged
on". (The .xlA files are NOT set-up as 'Add-Ins' in EXCEL.) These
macros are working as intended.
Now, I need to complete a similar macro that has a 'copy/paste from
WORD' procedure. I have been able to do this, using both late and
early binding, when my machine is "logged-on" to our network. However,
I also need to do this when my machine is "logged-off"; neither early
nor late binding is working -- the primary data file becomes "locked
for editing" by the network. (The VBA code is in a .xlA file stored on
my hard drive; a Scheduled Task opens this file at a specified time
each day.) The Scheduled Task fires, but becomes locked in "Running"
mode. We are running Office 2003 and Windows XP Pro.
Below is my early binding code procedure:
Public Sub Import_From_WORD_Tables()
Dim objWord As Word.Application
Dim objDoc As Word.Document
Dim wordvalue As Variant
Dim j As Integer
Dim lngErrNo As Long
Dim strErrSrc As String
Dim strErrDesc As String
On Error GoTo PROC_ERR
Application.DisplayAlerts = False
PrimaryDataWB.Activate '<<== this is primary data file
Set objWord = New Word.Application
objWord.Visible = False
Set objDoc = Documents.Open(myPathPF_BATCH & "T and A.rtf")
objDoc.Activate
'For "Rows" 1 - 8 in Table 1
For j = 1 To 8
wordvalue = objDoc.Tables(1).Columns(4).Cells(j + 2)
ActiveWorkbook.Sheets(3).Cells(j + 17, 11) =
Application.WorksheetFunction.Clean(wordvalue)
Next j
objDoc.Close
objWord.Quit
Set objDoc = Nothing
Set objWord = Nothing
Exit Sub
PROC_ERR:
lngErrNo = Err.Number
strErrSrc = "->ADJUSTMENT_TEST()->" & Err.Source
strErrDesc = Err.Description
'Disable error handling
On Error GoTo 0
Err.Raise lngErrNo, strErrSrc, strErrDesc
End Sub
I have been able to determine that the macro crashes at the 'Set
objWord = ...' statement above.
Since I am running this logged-off, the above error trapping is doing
nothing for me. Any ideas?
TIA, I appreciate your help.