I'm trying to write a script that creates a text file, closes it, opens it
and reads it and close it again. That part is working fine.

However, the process is in a loop, going through records in a database.
After it runs through the first line, I get an error message when I try to
set the text file as shown here:

Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
ForWriting, True)

So, is there a way to clear that right before the "Loop" so that I can use
it again?

Thanks.

"Glenn" <nospam@xxxxxx> wrote in message
news:e0PAxDiyIHA.3384@xxxxxx

> I'm trying to write a script that creates a text file, closes it, opens it
> and reads it and close it again. That part is working fine.
>
> However, the process is in a loop, going through records in a database.
> After it runs through the first line, I get an error message when I try to
> set the text file as shown here:
>
> Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
> ForWriting, True)
>
> So, is there a way to clear that right before the "Loop" so that I can use
> it again?
>
> Thanks.

It would help if you posted a little more of your code and
quoted the exact error message you see.

The error I'm getting is "Permission Denied" 800A0046, and it is on the line
that says "Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
ForWriting, True)

Here's my entire code:

BatchName = InputBox("Please enter the name of your batch:", _
"Batch Name")
If BatchName = "" Then
Wscript.Quit
Else
REM Wscript.Echo BatchName


End If


strComputer = "myserver"
Const adOpenStatic = 3
Const adLockOptimistic = 3


Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Set objRecordSet2 = CreateObject("ADODB.Recordset")
Set objRecordSet3 = CreateObject("ADODB.Recordset")
Set objRecordSet4 = CreateObject("ADODB.Recordset")


objConnection.Open _
"Provider=SQLOLEDB;Data Source=" & strComputer & ";" & _
"Trusted_Connection=Yes;Initial Catalog=MTA"

objRecordSet.Open "SELECT * FROM mytable where bachnumb ='" & BatchName &
"'", _
objConnection, adOpenStatic, adLockOptimistic



objRecordSet.MoveFirst


objRecordSet2.Open "SELECT * FROM mytable2 where Master_ID ='" &
objRecordset.Fields.Item("ORD") & "'", _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet3.Open "SELECT * FROM mytable3 where DOCNUMBR ='" &
objRecordset.Fields.Item("CHEKNMBR") & "'", _
objConnection, adOpenStatic, adLockOptimistic

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
ForWriting, True)

objLogFile.Write "We have processed your order"
objLogFile.Write "."
objLogFile.Writeline
objLogFile.Writeline
objLogFile.Write "This represents the following:"
objLogFile.Writeline
objLogFile.Writeline

objRecordSet4.Open "SELECT * FROM mytable4 where apfrdcnm ='" &
objRecordset.Fields.Item("CHEKNMBR") & "'", _
objConnection, adOpenStatic, adLockOptimistic

Do Until objRecordset4.EOF


objLogFile.Write objRecordset4.Fields.Item("aptodcnm")
objLogFile.Write " in the amount of $"
objLogFile.Write objRecordset4.Fields.Item("APPLDAMT")
objLogFile.WriteLine

objRecordset4.MoveNext

Loop


objLogFile.Writeline
objLogFile.Writeline

objLogFile.Close

Set objNetwork = CreateObject("Wscript.Network")
strUser = objNetwork.UserName

set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("c:\test.txt", ForReading)

StrText2 = objFile.ReadAll
objFile.Close

Set objEmail = CreateObject("CDO.Message")

objEmail.From = "me@xxxxxx"
objEmail.To = "me@xxxxxx"
objEmail.Subject = "Notification of Order"
objEmail.Textbody = StrText2

objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
"exchange"
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send



Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("ORMSTRID") & _
vbTab & objRecordset.Fields.Item("CHEKNMBR")
objRecordset.MoveNext
Loop















"Pegasus (MVP)" <I.can@xxxxxx> wrote in message
news:uQJ0zJiyIHA.1236@xxxxxx

>
> "Glenn" <nospam@xxxxxx> wrote in message
> news:e0PAxDiyIHA.3384@xxxxxx

>> I'm trying to write a script that creates a text file, closes it, opens
>> it and reads it and close it again. That part is working fine.
>>
>> However, the process is in a loop, going through records in a database.
>> After it runs through the first line, I get an error message when I try
>> to set the text file as shown here:
>>
>> Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
>> ForWriting, True)
>>
>> So, is there a way to clear that right before the "Loop" so that I can
>> use it again?
>>
>> Thanks.

>
> It would help if you posted a little more of your code and
> quoted the exact error message you see.
>

I'm afraid I cannot understand your report. In your first post
you said that the problem occurs when you run through your
loop. For clarity I have marked your loop with +++ in the text
below. You also said that the code that opens your log file is
causing the error. That line is marked with *** in the code
below. As you will see, it occurs well before the loop. How
can a line outside the loop cause an error when you step
through the loop?


"Glenn" <nospam@xxxxxx> wrote in message
news:OAa5YXiyIHA.1240@xxxxxx

> The error I'm getting is "Permission Denied" 800A0046, and it is on the
> line that says "Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
> ForWriting, True)
>
> Here's my entire code:
>
> BatchName = InputBox("Please enter the name of your batch:", _
> "Batch Name")
> If BatchName = "" Then
> Wscript.Quit
> Else
> REM Wscript.Echo BatchName
>
>
> End If
>
>
> strComputer = "myserver"
> Const adOpenStatic = 3
> Const adLockOptimistic = 3
>
>
> Set objConnection = CreateObject("ADODB.Connection")
> Set objRecordSet = CreateObject("ADODB.Recordset")
> Set objRecordSet2 = CreateObject("ADODB.Recordset")
> Set objRecordSet3 = CreateObject("ADODB.Recordset")
> Set objRecordSet4 = CreateObject("ADODB.Recordset")
>
>
> objConnection.Open _
> "Provider=SQLOLEDB;Data Source=" & strComputer & ";" & _
> "Trusted_Connection=Yes;Initial Catalog=MTA"
>
> objRecordSet.Open "SELECT * FROM mytable where bachnumb ='" & BatchName &
> "'", _
> objConnection, adOpenStatic, adLockOptimistic
>
>
>
> objRecordSet.MoveFirst
>
>
> objRecordSet2.Open "SELECT * FROM mytable2 where Master_ID ='" &
> objRecordset.Fields.Item("ORD") & "'", _
> objConnection, adOpenStatic, adLockOptimistic
>
> objRecordSet3.Open "SELECT * FROM mytable3 where DOCNUMBR ='" &
> objRecordset.Fields.Item("CHEKNMBR") & "'", _
> objConnection, adOpenStatic, adLockOptimistic
>
> Const ForReading = 1
> Const ForWriting = 2
>
> Set objFSO = CreateObject("Scripting.FileSystemObject")
> *** Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
> ForWriting, True)
>
> objLogFile.Write "We have processed your order"
> objLogFile.Write "."
> objLogFile.Writeline
> objLogFile.Writeline
> objLogFile.Write "This represents the following:"
> objLogFile.Writeline
> objLogFile.Writeline
>
> objRecordSet4.Open "SELECT * FROM mytable4 where apfrdcnm ='" &
> objRecordset.Fields.Item("CHEKNMBR") & "'", _
> objConnection, adOpenStatic, adLockOptimistic
>
> +++ Do Until objRecordset4.EOF
>
>
> objLogFile.Write objRecordset4.Fields.Item("aptodcnm")
> objLogFile.Write " in the amount of $"
> objLogFile.Write objRecordset4.Fields.Item("APPLDAMT")
> objLogFile.WriteLine
>
> objRecordset4.MoveNext
>
> +++ Loop
>
>
> objLogFile.Writeline
> objLogFile.Writeline
>
> objLogFile.Close
>
> Set objNetwork = CreateObject("Wscript.Network")
> strUser = objNetwork.UserName
>
> set objFSO = CreateObject("Scripting.FileSystemObject")
> Set objFile = objFSO.OpenTextFile("c:\test.txt", ForReading)
>
> StrText2 = objFile.ReadAll
> objFile.Close
>
> Set objEmail = CreateObject("CDO.Message")
>
> objEmail.From = "me@xxxxxx"
> objEmail.To = "me@xxxxxx"
> objEmail.Subject = "Notification of Order"
> objEmail.Textbody = StrText2
>
> objEmail.Configuration.Fields.Item _
> ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
> objEmail.Configuration.Fields.Item _
> ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
> "exchange"
> objEmail.Configuration.Fields.Item _
> ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
> objEmail.Configuration.Fields.Update
> objEmail.Send
>
>
>
> Do Until objRecordset.EOF
> Wscript.Echo objRecordset.Fields.Item("ORMSTRID") & _
> vbTab & objRecordset.Fields.Item("CHEKNMBR")
> objRecordset.MoveNext
> Loop
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> "Pegasus (MVP)" <I.can@xxxxxx> wrote in message
> news:uQJ0zJiyIHA.1236@xxxxxx

>>
>> "Glenn" <nospam@xxxxxx> wrote in message
>> news:e0PAxDiyIHA.3384@xxxxxx

>>> I'm trying to write a script that creates a text file, closes it, opens
>>> it and reads it and close it again. That part is working fine.
>>>
>>> However, the process is in a loop, going through records in a database.
>>> After it runs through the first line, I get an error message when I try
>>> to set the text file as shown here:
>>>
>>> Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
>>> ForWriting, True)
>>>
>>> So, is there a way to clear that right before the "Loop" so that I can
>>> use it again?
>>>
>>> Thanks.

>>
>> It would help if you posted a little more of your code and
>> quoted the exact error message you see.
>>

>
>

I apologize - I copied and pasted my script, but it appears as though it
didn't paste properly because there were a couple of things missing. I am
going to try posting it again. Basically, there are two loops in this.
When I run it, it cycles through one time competely fine (running the
"inner" loop during the creation of the text file. without a problem). It's
when processing the "outer" loop for the second time that the error occurs
when it gets to: Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
1, True)

I hope this is more helpful, sorry about the confusion.


------------------------------------------------------------------------------------
BatchName = InputBox("Please enter the name of your batch:", _
"Batch Name")
If BatchName = "" Then
Wscript.Quit
Else
REM Wscript.Echo BatchName


End If


strComputer = "acct"
Const adOpenStatic = 3
Const adLockOptimistic = 3


Const ForReading = 1
Const ForWriting = 2


Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Set objRecordSet2 = CreateObject("ADODB.Recordset")
Set objRecordSet3 = CreateObject("ADODB.Recordset")
Set objRecordSet4 = CreateObject("ADODB.Recordset")


objConnection.Open _
"Provider=SQLOLEDB;Data Source=" & strComputer & ";" & _
"Trusted_Connection=Yes;Initial Catalog=MAIN"

objRecordSet.Open "SELECT * FROM me234602 where bachnumb ='" & BatchName &
"'", _
objConnection, adOpenStatic, adLockOptimistic


objRecordSet.MoveFirst


Do Until objRecordset.EOF

objRecordSet2.Open "SELECT * FROM sy01200 where Master_ID ='" &
objRecordset.Fields.Item("ORMSTRID") & "'", _
objConnection, adOpenStatic, adLockOptimistic

WScript.Echo objRecordset2.Fields.Item("inet1")

objRecordSet3.Open "SELECT * FROM pm30200 where DOCNUMBR ='" &
objRecordset.Fields.Item("CHEKNMBR") & "'", _
objConnection, adOpenStatic, adLockOptimistic

WScript.Echo objRecordset3.Fields.Item("DOCAMNT")

'Const ForReading = 1
'Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
1, True)

objLogFile.Write "We have inititated a deposit to your bank account in the
amount of $"
objLogFile.Write objRecordSet3.Fields.Item("DOCAMNT")
objLogFile.Write "."
objLogFile.Writeline
objLogFile.Writeline
objLogFile.Write "This represents payment for:"
objLogFile.Writeline
objLogFile.Writeline

objRecordSet4.Open "SELECT * FROM pm30300 where apfrdcnm ='" &
objRecordset.Fields.Item("CHEKNMBR") & "'", _
objConnection, adOpenStatic, adLockOptimistic

Do Until objRecordset4.EOF


objLogFile.Write objRecordset4.Fields.Item("aptodcnm")
objLogFile.Write " in the amount of $"
objLogFile.Write objRecordset4.Fields.Item("APPLDAMT")
objLogFile.WriteLine

objRecordset4.MoveNext

Loop


objLogFile.Writeline
objLogFile.Writeline
objLogFile.Write "Please note that the time for funds to become available
varies"
objLogFile.Writeline
objLogFile.Write "from bank to bank."
objLogFile.Writeline
objLogFile.Writeline
objLogFile.Write "Thank you for using direct deposit!"

objLogFile.Close

'Set objNetwork = CreateObject("Wscript.Network")
'strUser = objNetwork.UserName

set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("c:\test.txt", ForReading)

StrText2 = objFile.ReadAll
objFile.Close

Set objEmail = CreateObject("CDO.Message")

objEmail.From = "me@xxxxxx"
objEmail.To = "me@xxxxxx"
objEmail.Subject = "Notification of Direct Deposit"
objEmail.Textbody = StrText2

objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
"exchange"
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send



'Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("ORMSTRID") & _
vbTab & objRecordset.Fields.Item("CHEKNMBR")
objRecordset.MoveNext
objRecordSet2.Close
objRecordSet3.Close
objRecordSet4.Close
Loop


"Pegasus (MVP)" <I.can@xxxxxx> wrote in message
news:eFL3d9iyIHA.6096@xxxxxx

> I'm afraid I cannot understand your report. In your first post
> you said that the problem occurs when you run through your
> loop. For clarity I have marked your loop with +++ in the text
> below. You also said that the code that opens your log file is
> causing the error. That line is marked with *** in the code
> below. As you will see, it occurs well before the loop. How
> can a line outside the loop cause an error when you step
> through the loop?
>
>
> "Glenn" <nospam@xxxxxx> wrote in message
> news:OAa5YXiyIHA.1240@xxxxxx

>> The error I'm getting is "Permission Denied" 800A0046, and it is on the
>> line that says "Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
>> ForWriting, True)
>>
>> Here's my entire code:
>>
>> BatchName = InputBox("Please enter the name of your batch:", _
>> "Batch Name")
>> If BatchName = "" Then
>> Wscript.Quit
>> Else
>> REM Wscript.Echo BatchName
>>
>>
>> End If
>>
>>
>> strComputer = "myserver"
>> Const adOpenStatic = 3
>> Const adLockOptimistic = 3
>>
>>
>> Set objConnection = CreateObject("ADODB.Connection")
>> Set objRecordSet = CreateObject("ADODB.Recordset")
>> Set objRecordSet2 = CreateObject("ADODB.Recordset")
>> Set objRecordSet3 = CreateObject("ADODB.Recordset")
>> Set objRecordSet4 = CreateObject("ADODB.Recordset")
>>
>>
>> objConnection.Open _
>> "Provider=SQLOLEDB;Data Source=" & strComputer & ";" & _
>> "Trusted_Connection=Yes;Initial Catalog=MTA"
>>
>> objRecordSet.Open "SELECT * FROM mytable where bachnumb ='" & BatchName
>> & "'", _
>> objConnection, adOpenStatic, adLockOptimistic
>>
>>
>>
>> objRecordSet.MoveFirst
>>
>>
>> objRecordSet2.Open "SELECT * FROM mytable2 where Master_ID ='" &
>> objRecordset.Fields.Item("ORD") & "'", _
>> objConnection, adOpenStatic, adLockOptimistic
>>
>> objRecordSet3.Open "SELECT * FROM mytable3 where DOCNUMBR ='" &
>> objRecordset.Fields.Item("CHEKNMBR") & "'", _
>> objConnection, adOpenStatic, adLockOptimistic
>>
>> Const ForReading = 1
>> Const ForWriting = 2
>>
>> Set objFSO = CreateObject("Scripting.FileSystemObject")
>> *** Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
>> ForWriting, True)
>>
>> objLogFile.Write "We have processed your order"
>> objLogFile.Write "."
>> objLogFile.Writeline
>> objLogFile.Writeline
>> objLogFile.Write "This represents the following:"
>> objLogFile.Writeline
>> objLogFile.Writeline
>>
>> objRecordSet4.Open "SELECT * FROM mytable4 where apfrdcnm ='" &
>> objRecordset.Fields.Item("CHEKNMBR") & "'", _
>> objConnection, adOpenStatic, adLockOptimistic
>>
>> +++ Do Until objRecordset4.EOF
>>
>>
>> objLogFile.Write objRecordset4.Fields.Item("aptodcnm")
>> objLogFile.Write " in the amount of $"
>> objLogFile.Write objRecordset4.Fields.Item("APPLDAMT")
>> objLogFile.WriteLine
>>
>> objRecordset4.MoveNext
>>
>> +++ Loop
>>
>>
>> objLogFile.Writeline
>> objLogFile.Writeline
>>
>> objLogFile.Close
>>
>> Set objNetwork = CreateObject("Wscript.Network")
>> strUser = objNetwork.UserName
>>
>> set objFSO = CreateObject("Scripting.FileSystemObject")
>> Set objFile = objFSO.OpenTextFile("c:\test.txt", ForReading)
>>
>> StrText2 = objFile.ReadAll
>> objFile.Close
>>
>> Set objEmail = CreateObject("CDO.Message")
>>
>> objEmail.From = "me@xxxxxx"
>> objEmail.To = "me@xxxxxx"
>> objEmail.Subject = "Notification of Order"
>> objEmail.Textbody = StrText2
>>
>> objEmail.Configuration.Fields.Item _
>> ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
>> objEmail.Configuration.Fields.Item _
>> ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
>> "exchange"
>> objEmail.Configuration.Fields.Item _
>> ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
>> objEmail.Configuration.Fields.Update
>> objEmail.Send
>>
>>
>>
>> Do Until objRecordset.EOF
>> Wscript.Echo objRecordset.Fields.Item("ORMSTRID") & _
>> vbTab & objRecordset.Fields.Item("CHEKNMBR")
>> objRecordset.MoveNext
>> Loop
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> "Pegasus (MVP)" <I.can@xxxxxx> wrote in message
>> news:uQJ0zJiyIHA.1236@xxxxxx

>>>
>>> "Glenn" <nospam@xxxxxx> wrote in message
>>> news:e0PAxDiyIHA.3384@xxxxxx
>>>> I'm trying to write a script that creates a text file, closes it, opens
>>>> it and reads it and close it again. That part is working fine.
>>>>
>>>> However, the process is in a loop, going through records in a database.
>>>> After it runs through the first line, I get an error message when I try
>>>> to set the text file as shown here:
>>>>
>>>> Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
>>>> ForWriting, True)
>>>>
>>>> So, is there a way to clear that right before the "Loop" so that I can
>>>> use it again?
>>>>
>>>> Thanks.
>>>
>>> It would help if you posted a little more of your code and
>>> quoted the exact error message you see.
>>>

>>
>>

>
>

"Glenn" <nospam@xxxxxx> wrote in message
news:OAa5YXiyIHA.1240@xxxxxx

> The error I'm getting is "Permission Denied" 800A0046, and it is on
> the line that says "Set objLogFile =
> objFSO.CreateTextFile("C:\test.txt", _
> ForWriting, True)
>
> Here's my entire code:
>
> BatchName = InputBox("Please enter the name of your batch:", _
> "Batch Name")
> If BatchName = "" Then
> Wscript.Quit
> Else
> REM Wscript.Echo BatchName
>
>
> End If
>
>
> strComputer = "myserver"
> Const adOpenStatic = 3
> Const adLockOptimistic = 3
>
>
> Set objConnection = CreateObject("ADODB.Connection")
> Set objRecordSet = CreateObject("ADODB.Recordset")
> Set objRecordSet2 = CreateObject("ADODB.Recordset")
> Set objRecordSet3 = CreateObject("ADODB.Recordset")
> Set objRecordSet4 = CreateObject("ADODB.Recordset")
>
>
> objConnection.Open _
> "Provider=SQLOLEDB;Data Source=" & strComputer & ";" & _
> "Trusted_Connection=Yes;Initial Catalog=MTA"
>
> objRecordSet.Open "SELECT * FROM mytable where bachnumb ='" &
> BatchName & "'", _
> objConnection, adOpenStatic, adLockOptimistic
>
>
>
> objRecordSet.MoveFirst
>
>
> objRecordSet2.Open "SELECT * FROM mytable2 where Master_ID ='" &
> objRecordset.Fields.Item("ORD") & "'", _
> objConnection, adOpenStatic, adLockOptimistic
>
> objRecordSet3.Open "SELECT * FROM mytable3 where DOCNUMBR ='" &
> objRecordset.Fields.Item("CHEKNMBR") & "'", _
> objConnection, adOpenStatic, adLockOptimistic
>
> Const ForReading = 1
> Const ForWriting = 2
>
> Set objFSO = CreateObject("Scripting.FileSystemObject")
> Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
> ForWriting, True)
>
> objLogFile.Write "We have processed your order"
> objLogFile.Write "."
> objLogFile.Writeline
> objLogFile.Writeline
> objLogFile.Write "This represents the following:"
> objLogFile.Writeline
> objLogFile.Writeline
>
> objRecordSet4.Open "SELECT * FROM mytable4 where apfrdcnm ='" &
> objRecordset.Fields.Item("CHEKNMBR") & "'", _
> objConnection, adOpenStatic, adLockOptimistic
>
> Do Until objRecordset4.EOF
>
>
> objLogFile.Write objRecordset4.Fields.Item("aptodcnm")
> objLogFile.Write " in the amount of $"
> objLogFile.Write objRecordset4.Fields.Item("APPLDAMT")
> objLogFile.WriteLine
>
> objRecordset4.MoveNext
>
> Loop
>
>
> objLogFile.Writeline
> objLogFile.Writeline
>
> objLogFile.Close
>
> Set objNetwork = CreateObject("Wscript.Network")
> strUser = objNetwork.UserName
>
> set objFSO = CreateObject("Scripting.FileSystemObject")
> Set objFile = objFSO.OpenTextFile("c:\test.txt", ForReading)
>
> StrText2 = objFile.ReadAll
> objFile.Close
>
> Set objEmail = CreateObject("CDO.Message")
>
> objEmail.From = "me@xxxxxx"
> objEmail.To = "me@xxxxxx"
> objEmail.Subject = "Notification of Order"
> objEmail.Textbody = StrText2
>
> objEmail.Configuration.Fields.Item _
> ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
> objEmail.Configuration.Fields.Item _
> ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
> "exchange"
> objEmail.Configuration.Fields.Item _
> ("http://schemas.microsoft.com/cdo/configuration/smtpserverport")
> = 25
> objEmail.Configuration.Fields.Update
> objEmail.Send
>
>
>
> Do Until objRecordset.EOF
> Wscript.Echo objRecordset.Fields.Item("ORMSTRID") & _
> vbTab & objRecordset.Fields.Item("CHEKNMBR")
> objRecordset.MoveNext
> Loop

It appears to me that you repeatedly overwrite file "c:\test.txt" with
stuff associated with an order, then read it and email its contents.

My first suggestion would be to just concatenate the info in a string,
rather than writing it to a file, and email that string.

If you need to create a file and append stuff to it and later read it,
then I would suggest groups.google, searching the scripting groups for
routines named fOverwrite and fAppend. These routines are rather
inefficient in that they close the textstream/file after each write to
the file, but that means that you can mindlessly, at any point in the
script, open the file at any time and read it (being sure to close
again). fOverwrite lets you reinitialize a file to an empty string or
whatever. You can guess what fAppend does from its name.

-Paul Randall

Your solution sounds great, I don't need a text file for anything, I just
couldn't figure out another way. I'm not really sure how to concatenate the
info in a string, though.


"Paul Randall" <paulr901@xxxxxx> wrote in message
news:eAYA2FjyIHA.5520@xxxxxx

>
> "Glenn" <nospam@xxxxxx> wrote in message
> news:OAa5YXiyIHA.1240@xxxxxx

>> The error I'm getting is "Permission Denied" 800A0046, and it is on the
>> line that says "Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
>> ForWriting, True)
>>
>> Here's my entire code:
>>
>> BatchName = InputBox("Please enter the name of your batch:", _
>> "Batch Name")
>> If BatchName = "" Then
>> Wscript.Quit
>> Else
>> REM Wscript.Echo BatchName
>>
>>
>> End If
>>
>>
>> strComputer = "myserver"
>> Const adOpenStatic = 3
>> Const adLockOptimistic = 3
>>
>>
>> Set objConnection = CreateObject("ADODB.Connection")
>> Set objRecordSet = CreateObject("ADODB.Recordset")
>> Set objRecordSet2 = CreateObject("ADODB.Recordset")
>> Set objRecordSet3 = CreateObject("ADODB.Recordset")
>> Set objRecordSet4 = CreateObject("ADODB.Recordset")
>>
>>
>> objConnection.Open _
>> "Provider=SQLOLEDB;Data Source=" & strComputer & ";" & _
>> "Trusted_Connection=Yes;Initial Catalog=MTA"
>>
>> objRecordSet.Open "SELECT * FROM mytable where bachnumb ='" & BatchName
>> & "'", _
>> objConnection, adOpenStatic, adLockOptimistic
>>
>>
>>
>> objRecordSet.MoveFirst
>>
>>
>> objRecordSet2.Open "SELECT * FROM mytable2 where Master_ID ='" &
>> objRecordset.Fields.Item("ORD") & "'", _
>> objConnection, adOpenStatic, adLockOptimistic
>>
>> objRecordSet3.Open "SELECT * FROM mytable3 where DOCNUMBR ='" &
>> objRecordset.Fields.Item("CHEKNMBR") & "'", _
>> objConnection, adOpenStatic, adLockOptimistic
>>
>> Const ForReading = 1
>> Const ForWriting = 2
>>
>> Set objFSO = CreateObject("Scripting.FileSystemObject")
>> Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
>> ForWriting, True)
>>
>> objLogFile.Write "We have processed your order"
>> objLogFile.Write "."
>> objLogFile.Writeline
>> objLogFile.Writeline
>> objLogFile.Write "This represents the following:"
>> objLogFile.Writeline
>> objLogFile.Writeline
>>
>> objRecordSet4.Open "SELECT * FROM mytable4 where apfrdcnm ='" &
>> objRecordset.Fields.Item("CHEKNMBR") & "'", _
>> objConnection, adOpenStatic, adLockOptimistic
>>
>> Do Until objRecordset4.EOF
>>
>>
>> objLogFile.Write objRecordset4.Fields.Item("aptodcnm")
>> objLogFile.Write " in the amount of $"
>> objLogFile.Write objRecordset4.Fields.Item("APPLDAMT")
>> objLogFile.WriteLine
>>
>> objRecordset4.MoveNext
>>
>> Loop
>>
>>
>> objLogFile.Writeline
>> objLogFile.Writeline
>>
>> objLogFile.Close
>>
>> Set objNetwork = CreateObject("Wscript.Network")
>> strUser = objNetwork.UserName
>>
>> set objFSO = CreateObject("Scripting.FileSystemObject")
>> Set objFile = objFSO.OpenTextFile("c:\test.txt", ForReading)
>>
>> StrText2 = objFile.ReadAll
>> objFile.Close
>>
>> Set objEmail = CreateObject("CDO.Message")
>>
>> objEmail.From = "me@xxxxxx"
>> objEmail.To = "me@xxxxxx"
>> objEmail.Subject = "Notification of Order"
>> objEmail.Textbody = StrText2
>>
>> objEmail.Configuration.Fields.Item _
>> ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
>> objEmail.Configuration.Fields.Item _
>> ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
>> "exchange"
>> objEmail.Configuration.Fields.Item _
>> ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
>> objEmail.Configuration.Fields.Update
>> objEmail.Send
>>
>>
>>
>> Do Until objRecordset.EOF
>> Wscript.Echo objRecordset.Fields.Item("ORMSTRID") & _
>> vbTab & objRecordset.Fields.Item("CHEKNMBR")
>> objRecordset.MoveNext
>> Loop

>
> It appears to me that you repeatedly overwrite file "c:\test.txt" with
> stuff associated with an order, then read it and email its contents.
>
> My first suggestion would be to just concatenate the info in a string,
> rather than writing it to a file, and email that string.
>
> If you need to create a file and append stuff to it and later read it,
> then I would suggest groups.google, searching the scripting groups for
> routines named fOverwrite and fAppend. These routines are rather
> inefficient in that they close the textstream/file after each write to the
> file, but that means that you can mindlessly, at any point in the script,
> open the file at any time and read it (being sure to close again).
> fOverwrite lets you reinitialize a file to an empty string or whatever.
> You can guess what fAppend does from its name.
>
> -Paul Randall
>

When concatenating info into a string, how do you insert a return so that
when you use the string it looks like this:

My test
goes here


instead of:
My text goes here

Thanks.
"Paul Randall" <paulr901@xxxxxx> wrote in message
news:eAYA2FjyIHA.5520@xxxxxx

>
> "Glenn" <nospam@xxxxxx> wrote in message
> news:OAa5YXiyIHA.1240@xxxxxx

>> The error I'm getting is "Permission Denied" 800A0046, and it is on the
>> line that says "Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
>> ForWriting, True)
>>
>> Here's my entire code:
>>
>> BatchName = InputBox("Please enter the name of your batch:", _
>> "Batch Name")
>> If BatchName = "" Then
>> Wscript.Quit
>> Else
>> REM Wscript.Echo BatchName
>>
>>
>> End If
>>
>>
>> strComputer = "myserver"
>> Const adOpenStatic = 3
>> Const adLockOptimistic = 3
>>
>>
>> Set objConnection = CreateObject("ADODB.Connection")
>> Set objRecordSet = CreateObject("ADODB.Recordset")
>> Set objRecordSet2 = CreateObject("ADODB.Recordset")
>> Set objRecordSet3 = CreateObject("ADODB.Recordset")
>> Set objRecordSet4 = CreateObject("ADODB.Recordset")
>>
>>
>> objConnection.Open _
>> "Provider=SQLOLEDB;Data Source=" & strComputer & ";" & _
>> "Trusted_Connection=Yes;Initial Catalog=MTA"
>>
>> objRecordSet.Open "SELECT * FROM mytable where bachnumb ='" & BatchName
>> & "'", _
>> objConnection, adOpenStatic, adLockOptimistic
>>
>>
>>
>> objRecordSet.MoveFirst
>>
>>
>> objRecordSet2.Open "SELECT * FROM mytable2 where Master_ID ='" &
>> objRecordset.Fields.Item("ORD") & "'", _
>> objConnection, adOpenStatic, adLockOptimistic
>>
>> objRecordSet3.Open "SELECT * FROM mytable3 where DOCNUMBR ='" &
>> objRecordset.Fields.Item("CHEKNMBR") & "'", _
>> objConnection, adOpenStatic, adLockOptimistic
>>
>> Const ForReading = 1
>> Const ForWriting = 2
>>
>> Set objFSO = CreateObject("Scripting.FileSystemObject")
>> Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
>> ForWriting, True)
>>
>> objLogFile.Write "We have processed your order"
>> objLogFile.Write "."
>> objLogFile.Writeline
>> objLogFile.Writeline
>> objLogFile.Write "This represents the following:"
>> objLogFile.Writeline
>> objLogFile.Writeline
>>
>> objRecordSet4.Open "SELECT * FROM mytable4 where apfrdcnm ='" &
>> objRecordset.Fields.Item("CHEKNMBR") & "'", _
>> objConnection, adOpenStatic, adLockOptimistic
>>
>> Do Until objRecordset4.EOF
>>
>>
>> objLogFile.Write objRecordset4.Fields.Item("aptodcnm")
>> objLogFile.Write " in the amount of $"
>> objLogFile.Write objRecordset4.Fields.Item("APPLDAMT")
>> objLogFile.WriteLine
>>
>> objRecordset4.MoveNext
>>
>> Loop
>>
>>
>> objLogFile.Writeline
>> objLogFile.Writeline
>>
>> objLogFile.Close
>>
>> Set objNetwork = CreateObject("Wscript.Network")
>> strUser = objNetwork.UserName
>>
>> set objFSO = CreateObject("Scripting.FileSystemObject")
>> Set objFile = objFSO.OpenTextFile("c:\test.txt", ForReading)
>>
>> StrText2 = objFile.ReadAll
>> objFile.Close
>>
>> Set objEmail = CreateObject("CDO.Message")
>>
>> objEmail.From = "me@xxxxxx"
>> objEmail.To = "me@xxxxxx"
>> objEmail.Subject = "Notification of Order"
>> objEmail.Textbody = StrText2
>>
>> objEmail.Configuration.Fields.Item _
>> ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
>> objEmail.Configuration.Fields.Item _
>> ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
>> "exchange"
>> objEmail.Configuration.Fields.Item _
>> ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
>> objEmail.Configuration.Fields.Update
>> objEmail.Send
>>
>>
>>
>> Do Until objRecordset.EOF
>> Wscript.Echo objRecordset.Fields.Item("ORMSTRID") & _
>> vbTab & objRecordset.Fields.Item("CHEKNMBR")
>> objRecordset.MoveNext
>> Loop

>
> It appears to me that you repeatedly overwrite file "c:\test.txt" with
> stuff associated with an order, then read it and email its contents.
>
> My first suggestion would be to just concatenate the info in a string,
> rather than writing it to a file, and email that string.
>
> If you need to create a file and append stuff to it and later read it,
> then I would suggest groups.google, searching the scripting groups for
> routines named fOverwrite and fAppend. These routines are rather
> inefficient in that they close the textstream/file after each write to the
> file, but that means that you can mindlessly, at any point in the script,
> open the file at any time and read it (being sure to close again).
> fOverwrite lets you reinitialize a file to an empty string or whatever.
> You can guess what fAppend does from its name.
>
> -Paul Randall
>

"Glenn" <nospam@xxxxxx> wrote in message
news:uxLXpNjyIHA.5620@xxxxxx

> Your solution sounds great, I don't need a text file for anything, I
> just couldn't figure out another way. I'm not really sure how to
> concatenate the info in a string, though.

Each place you create the file, instead create an empty string, like:
sTemp = ""

Each place you write something to that file, instead append that
something to your string:
sTemp = sTemp & "We have processed your order"

Each place you writeln something to that file, instead append that
something to your string, plus a carriage return and line feed:
sTemp = sTemp & "" & vbCrLf

-Paul Randall

text = "line one" & vbnewline
text = text & "line two" & vbnewline

/Al

"Glenn" <nospam@xxxxxx> wrote in message
news:OE4ZvejyIHA.5832@xxxxxx

> When concatenating info into a string, how do you insert a return so that
> when you use the string it looks like this:
>
> My test
> goes here
>
>
> instead of:
> My text goes here
>
> Thanks.
> "Paul Randall" <paulr901@xxxxxx> wrote in message
> news:eAYA2FjyIHA.5520@xxxxxx

>>
>> "Glenn" <nospam@xxxxxx> wrote in message
>> news:OAa5YXiyIHA.1240@xxxxxx

>>> The error I'm getting is "Permission Denied" 800A0046, and it is on the
>>> line that says "Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
>>> ForWriting, True)
>>>
>>> Here's my entire code:
>>>
>>> BatchName = InputBox("Please enter the name of your batch:", _
>>> "Batch Name")
>>> If BatchName = "" Then
>>> Wscript.Quit
>>> Else
>>> REM Wscript.Echo BatchName
>>>
>>>
>>> End If
>>>
>>>
>>> strComputer = "myserver"
>>> Const adOpenStatic = 3
>>> Const adLockOptimistic = 3
>>>
>>>
>>> Set objConnection = CreateObject("ADODB.Connection")
>>> Set objRecordSet = CreateObject("ADODB.Recordset")
>>> Set objRecordSet2 = CreateObject("ADODB.Recordset")
>>> Set objRecordSet3 = CreateObject("ADODB.Recordset")
>>> Set objRecordSet4 = CreateObject("ADODB.Recordset")
>>>
>>>
>>> objConnection.Open _
>>> "Provider=SQLOLEDB;Data Source=" & strComputer & ";" & _
>>> "Trusted_Connection=Yes;Initial Catalog=MTA"
>>>
>>> objRecordSet.Open "SELECT * FROM mytable where bachnumb ='" & BatchName
>>> & "'", _
>>> objConnection, adOpenStatic, adLockOptimistic
>>>
>>>
>>>
>>> objRecordSet.MoveFirst
>>>
>>>
>>> objRecordSet2.Open "SELECT * FROM mytable2 where Master_ID ='" &
>>> objRecordset.Fields.Item("ORD") & "'", _
>>> objConnection, adOpenStatic, adLockOptimistic
>>>
>>> objRecordSet3.Open "SELECT * FROM mytable3 where DOCNUMBR ='" &
>>> objRecordset.Fields.Item("CHEKNMBR") & "'", _
>>> objConnection, adOpenStatic, adLockOptimistic
>>>
>>> Const ForReading = 1
>>> Const ForWriting = 2
>>>
>>> Set objFSO = CreateObject("Scripting.FileSystemObject")
>>> Set objLogFile = objFSO.CreateTextFile("C:\test.txt", _
>>> ForWriting, True)
>>>
>>> objLogFile.Write "We have processed your order"
>>> objLogFile.Write "."
>>> objLogFile.Writeline
>>> objLogFile.Writeline
>>> objLogFile.Write "This represents the following:"
>>> objLogFile.Writeline
>>> objLogFile.Writeline
>>>
>>> objRecordSet4.Open "SELECT * FROM mytable4 where apfrdcnm ='" &
>>> objRecordset.Fields.Item("CHEKNMBR") & "'", _
>>> objConnection, adOpenStatic, adLockOptimistic
>>>
>>> Do Until objRecordset4.EOF
>>>
>>>
>>> objLogFile.Write objRecordset4.Fields.Item("aptodcnm")
>>> objLogFile.Write " in the amount of $"
>>> objLogFile.Write objRecordset4.Fields.Item("APPLDAMT")
>>> objLogFile.WriteLine
>>>
>>> objRecordset4.MoveNext
>>>
>>> Loop
>>>
>>>
>>> objLogFile.Writeline
>>> objLogFile.Writeline
>>>
>>> objLogFile.Close
>>>
>>> Set objNetwork = CreateObject("Wscript.Network")
>>> strUser = objNetwork.UserName
>>>
>>> set objFSO = CreateObject("Scripting.FileSystemObject")
>>> Set objFile = objFSO.OpenTextFile("c:\test.txt", ForReading)
>>>
>>> StrText2 = objFile.ReadAll
>>> objFile.Close
>>>
>>> Set objEmail = CreateObject("CDO.Message")
>>>
>>> objEmail.From = "me@xxxxxx"
>>> objEmail.To = "me@xxxxxx"
>>> objEmail.Subject = "Notification of Order"
>>> objEmail.Textbody = StrText2
>>>
>>> objEmail.Configuration.Fields.Item _
>>> ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
>>> objEmail.Configuration.Fields.Item _
>>> ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
>>> "exchange"
>>> objEmail.Configuration.Fields.Item _
>>> ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") =
>>> 25
>>> objEmail.Configuration.Fields.Update
>>> objEmail.Send
>>>
>>>
>>>
>>> Do Until objRecordset.EOF
>>> Wscript.Echo objRecordset.Fields.Item("ORMSTRID") & _
>>> vbTab & objRecordset.Fields.Item("CHEKNMBR")
>>> objRecordset.MoveNext
>>> Loop

>>
>> It appears to me that you repeatedly overwrite file "c:\test.txt" with
>> stuff associated with an order, then read it and email its contents.
>>
>> My first suggestion would be to just concatenate the info in a string,
>> rather than writing it to a file, and email that string.
>>
>> If you need to create a file and append stuff to it and later read it,
>> then I would suggest groups.google, searching the scripting groups for
>> routines named fOverwrite and fAppend. These routines are rather
>> inefficient in that they close the textstream/file after each write to
>> the file, but that means that you can mindlessly, at any point in the
>> script, open the file at any time and read it (being sure to close
>> again). fOverwrite lets you reinitialize a file to an empty string or
>> whatever. You can guess what fAppend does from its name.
>>
>> -Paul Randall
>>

>
>