what goes wrong with this code?

[Guest]

Hello,
I have developed an application that is used in 4 different place to collect
data. Once in a while each center sends me back the data and after having
merged them all I perform some checking.
To deliver to each center the changes I make to data, I usually use another
very small db which just runs few queries through code and it's all done.
I was trying to add few coding to create also a .txt file which will show
the changes that was made to the data.
Her is the code (I'm listing just the portion where I get trouble with):

Option Compare Database
Private F As Object
Private wrkSec As Workspace
Private DBEng As Object
Private DBdaAgg As Database
Private centro As String
______________________________

Private Sub Command0_Click()
On Error GoTo HANDLER

Set fs = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
Dim FirstAction As String
Dim STATUS As Boolean
Dim wrkSec As Workspace
Dim QualePath As String
Dim TBLdaAgg As DAO.Recordset
Dim TBLmaster As DAO.Recordset
Dim logStringa As String
Dim ErrorLine As Byte
Set DBEng = CreateObject("DAO.DBEngine.36")
Set F = fs.CREATETEXTFILE("C:\FARUS\LOG_20MAY2007.TXT", True)
F.writeline ("*********************************************")
F.writeline ("** UPDATER DEL 20 MAGGIO 2007 (R) **")
F.writeline ("*********************************************")
F.writeline ("procedura lanciata il: " & Now())


This ocde works perfectly on my pc while it fires an error on the line
Set F = fs.CREATETEXTFILE("C:\FARUS\LOG_20MAY2007.TXT", True)
on the user's pc. I have tested it on others pcs and it works...
What can be wrong?
I'm guessing something with referencies?
The error message is the most generic possible:
"object variable not set ..."

Any suggestion?

Thanks!
Rocco

 

[BillCo]

Set fs = CreateObject("SCRIPTING.FILESYSTEMOBJECT")

you never declared the variable fs, so of course it will fail here.
Try adding Dim fs As Object to the declairations. I have no answer to
why it works on other machines - are you sure it's the exact same
code?

I'm guessing something with referencies?

Not in this case, but unless you are absolutely sure that every
machine that will run the database has the exact same software
installed then stay away from external com objects (and adcive x
componants). My advice would be to re-code this using standard VBA
procedure calls. I find this method works great:

Dim intFileNo as Integer
intFileNo = FreeFile
Open "C:\Temp\temp.txt" For Output As #intFileNo
Print #intFileNo, "test text"
Close

 

[Guest]

Thanks for your reply,
but why it works fine on my pc?
If it was a matter of not having declared the variable it shouldn't work
even on my.
boh...

I know the standard vba code will work, I have tried, but still I don't
understand why my code wont work. At least not on all the machines.

 

[BillCo]

I know the standard vba code will work, I have tried, but still I don't
understand why my code wont work. At least not on all the machines.

taking another look, i think your original instinct may have been
right - the references. the line of code may execute on your pc
without fs being declared - if you haven't used the "Option Explicit"
option at the top of your module - so it was a flaw in the code, but
not the one which is causing the problem.

Scripting.FileSystemObject is a COM object, and as such it needs to be
installed on the machine and registered and referenced. The other PCs
may have different versions or may not even have it at all, so the
reference breaks. There are ways to initialise your references on
start-up, but I stand by my initial recomendation of avoiding this in
favour of standard VBA procedural calls wherever possible in a
distributed application.

 

[Guest]

yes, I guess referencing is the issue here.
But what should be referenced?

 

[BillCo]

I honestly dont know. Scripting.FileSystemObject is designed for web
servers really... asp stuff - google it. I'm sure you'll get you
answer. and then re-write with something normal