Excel 2003 VBA: Import Numerical Data from .rtf File into .xlS Fi

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 my primary data file;
stored on my hard drive
Set objWord = New Word.Application
objWord.Visible = False

Set objDoc = Documents.Open(myPathBATCH & "T and A.rtf")
'the above file is stored on my hard
drive
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.

Cindy
Tue Nov 07 08:08:03 CST 2006

Hi =?Utf-8?B?SmluZ2xlUm9jaw==?=,

> 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.
>
Since you don't tell us what the error is that's generated by running the line
Set objWord = New Word.Application, it's difficult to even guess. It would also
help to know what, exactly, is on the network and what is on your machine. Is
Word even installed on your machine?

> 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 my primary data file;
> stored on my hard drive
> Set objWord = New Word.Application
> objWord.Visible = False
>
> Set objDoc = Documents.Open(myPathBATCH & "T and A.rtf")
> 'the above file is stored on my hard
> drive
> 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.
>

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :-)

JingleRock
Wed Nov 08 00:16:02 CST 2006

Cindy,
Thanks for your post.

The reason I did not identify the error is because my weak error-trapping
for a "logged off" situation did not tell me. I have since added a
'SendErrorEmail' Sub that now indicates that it is Error No. 70 - "Permission
denied".

All of the Office 2003 Applications are installed on my machine.
Some background: I am on a large corporate network, but there are only 20
users for this business-day daily process. We are running Windows XP Pro
SP-2. One option is to run this process on an Autosys server; however, there
are several drawbacks: including administrative red-tape and the data files
that are updated daily sometimes need to be revised, perhaps two weeks after
the first data is reported. So, I am thinking about running this process on
my machine; one of the drawbacks is that frequently our LAN Admin pushes
software updates down to each of the workstations at night -- this re-boots
our machines. So, the idea is to write VBA code on my C: drive, containing
an 'Auto_Open' stmt that is triggered by a Windows XP Scheduled Task that is
scheduled to fire at a specified time on each business day. I want this code
to execute when my machine is "logged on" and when it is "logged off". The
VBA Projects are digitally signed by me. The VBA Code is in an .xlA file
stored on my C: drive; this file is not set-up as an 'AddIn' in EXCEL.

Other Code Changes: I am now Dimming objDoc As Document; I deleted
'objWord.Visible = False'; and I deleted 'objDoc.Activate'.

All below refers to my machine running "logged off":
I am currently focusing my experimentation on opening the .rtf data file,
making a few edits, and then saving it back to a shared network drive; I am
not currently referring whatsoever to my .xlS primary data file. My Code is
behaving better:
the Scheduled Task is completing its Task; the WINWORD file is not
"hanging"; and
I am receiving Error E-Mails indicating Error No. 70.

I think there is some problem having two instances of Office Applications
open at the same time (I am able, using WORD VBA [NO references to EXCEL],
open up the same .rtf file, make changes in it, and then save it back to the
shared network drive, all on a "logged off" basis). However, my EXCEL Code
is able to send me e-mails (using CDO and at least a portion of OUTLOOK) from
our SMTP Server, on a "logged off" basis.

"Cindy M." wrote:
> Since you don't tell us what the error is that's generated by running the line
> Set objWord = New Word.Application, it's difficult to even guess. It would also
> help to know what, exactly, is on the network and what is on your machine. Is
> Word even installed on your machine?
>
> > 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 my primary data file;
> > stored on my hard drive
> > Set objWord = New Word.Application
> > objWord.Visible = False
> >
> > Set objDoc = Documents.Open(myPathBATCH & "T and A.rtf")
> > 'the above file is stored on my hard
> > drive
> > 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.
> >
>
> Cindy Meister
> INTER-Solutions, Switzerland
> http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
> http://www.word.mvps.org
>
> This reply is posted in the Newsgroup; please post any follow question or reply
> in the newsgroup and not by e-mail :-)
>
>

JingleRock
Wed Nov 08 12:46:01 CST 2006

I thought it would be useful to post my current code. When "logged on", this
code works just fine. When "logged off", this code fails at Line 2 (create
WORD Application Object). In both situations, there is no "hanging" of
WINWORD.

Option Explicit
Private Const OBJ_NAME = "modWORD_Data"
'***************************************************************************************************
'Public Sub Import_From_WORD_Tables()
Public Sub AUTO_OPEN()

Dim objWord As Word.Application
Dim objDoc As Document

Dim DataPath As String
Dim Totals_and_Averages_Data As String
Dim mywdRange As Word.Range
Dim strMsg As String

Dim lngErrNo As Long
Dim strErrSrc As String
Dim strErrDesc As String

On Error GoTo PROC_ERR

Application.DisplayAlerts = False
'************************************** NAMED PATHs
*********************************************
DataPath = "\\houdata01\Investments\Fixed_Income\Muni\DATA"
'
'************************************** NAMED FILEs
***************************************************
1: Totals_and_Averages_Data = DataPath & "\" & "T and A.DOC"
'
2: Set objWord = CreateObject("Word.Application")
'
3: Set objDoc = Documents.Open(Totals_and_Averages_Data)
'
4: Set mywdRange = objDoc.Words(1)
'
With mywdRange
.Text = "This text is a test."
.Font.Name = "Comic Sans MS"
.Font.Size = 14
.Font.ColorIndex = wdGreen
.Bold = True
End With

If Not (objDoc Is Nothing) Then
Set mywdRange = Nothing
objDoc.Save
objDoc.Close
Set objDoc = Nothing
End If

If Not (objWord Is Nothing) Then
objWord.Quit
Set objWord = Nothing
End If

Application.Quit
End

Exit Sub

PROC_ERR:

lngErrNo = Err.Number
strErrSrc = "Import_From_WORD_Tables()-->" & OBJ_NAME & "-->" & Err.Source
strErrDesc = Err.Description

strMsg = "Error No: " & lngErrNo & "<br><br>" & _
"Error Source: " & strErrSrc & "<br><br>" & "Error Msg: " &
strErrDesc & "<br><br>" & _
"The Code failed at Line " & Erl & "."

SendErrorEmail "Opening WORD File for 'Totals and Averages' Data
Failed", strMsg

'Disable error handling
On Error GoTo 0

If Not (objDoc Is Nothing) Then
Set mywdRange = Nothing
objDoc.Close
Set objDoc = Nothing
End If

If Not (objWord Is Nothing) Then
objWord.Quit
Set objWord = Nothing
End If

Application.Quit
End

End Sub

JingleRock
Thu Nov 09 16:12:02 CST 2006

MORE RESEARCH:

I made two code changes: 'Dim objDoc As Word.Document' (instead of As
Document) and
'Set objDoc = objWord.Documents.Open(Totals_and_Averages_Data)' [instead of
Set objDoc = Documents.Open(Totals_and_Averages_Data].

Obviously, my Code is now more consistent with conventional wisdom.
Somewhat surprising, to me, is that the results I obtained, both "logged on"
and "logged off", are identical to what I was getting before the changes.

Have you tried to replicate what I am getting with this Code?