Hi Folks

I have a 'File Grabber' DTS I'm working on that's written in ActiveX.

It crawls directories based on file patterns and does the appropriate
processing
for possibly dozens of files and of various types.

For some reason, executing Shell is blowing up with object required (sample
fragment):

' decide how to process this in SQL Server

Select Case UCase(getExt(oFile.Name))
Case "XLS"
' example:"[isql -b -S s14\PS -U myuser -Pmypwd -d TIC -o
"C:\Logs\TIC_2004_09_29_091155s.log" -Q "DECLARE @STR varchar (2000);SET
ANSI_NULLS ON;SET ANSI_WARNINGS ON;SET @STR = 'SELECT * INTO XLSImport FROM
OPENROWSET (''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=C:\psplm
NEW\TIC\FileExists\XLS\TIC_2004_09_29_091155s.xls' ',''SELECT * FROM [APP CR$]
WHERE PNO IS NOT NULL'')';Exec (@str)"]"

'Declare Variables
Dim mycmd , WshShelll,
oExec, LgName

mycmd = "isql -b -S s14\PS -U myuser -P mypwd -d TIC -t 7800 -o " &
Chr(34)
LgName = getPath(sLogFile) & getName(oFile.Name) & chr(Int((26 * Rnd) +
1) + 64) & ".log"
oLogFile.WriteLine("LgName: " & LgName)
mycmd = mycmd & LgName
' mycmd = mycmd & sLogFilePath & sLogFolder
& "\" & getName(oFile.Name) & chr(Int((26 * Rnd) + 1) + 64) & ".log"
mycmd = mycmd & Chr(34) & " -Q " & Chr(34)
& " DECLARE @STR varchar(2000);SET ANSI_NULLS ON;SET ANSI_WARNINGS ON;SET
@STR=" & Chr(39)
mycmd = mycmd & "SELECT * INTO XLSImport_"
mycmd = mycmd & getName(LgName)
mycmd = mycmd & " FROM OPENROWSET (" & Chr(39) & Chr(39) &
"Microsoft.Jet.OLEDB.4.0" & Chr(39) & Chr(39) & ","
mycmd = mycmd & Chr(39) & Chr(39) & "Excel 8.0;Database=" & oFile.Path
mycmd = mycmd & Chr(39) & Chr(39) & "," & Chr(39) & Chr(39) & "SELECT
* FROM [APP CR$] WHERE PMNO IS NOT NULL"
mycmd = mycmd & Chr(39) & Chr(39) & ")" &
Chr(39) & ";Exec (@str)" & Chr(34)
oLogFile.WriteLine("mycmd: " & mycmd)
'Set WshShell = WScript.CreateObject("WScript.Shell")
' runs OK as test->Set oExec = WshShell.Exec("calc")
PROBLEM-> thinks OBJECT Required? Set oExec = WshShell.Exec(mycmd)
'WshShell.LogEvent 4, "Started XLS script for " & LgName
'WshShell.Run mycmd, 0, TRUE
'WshShell.LogEvent 4, "Ended XLS script for " & LgName

' Clean Up
'set WshShell = Nothing
' set mycmd = Nothing
Call FileMove(oLogFile, sErrFile, sProcdFile, oFile.Path,
oFile.Name, True)
Case "XML"
:
:
looking at the value of 'mycmd' from the log it looks perfect (can copy and
paste and execute at cmd prompt)

any advice (other than creating a file as input - this would really be alot
of overhead)

Thanks Rob

darnedest thing is that if I comment out the Function Main() and End
Function, then add a 'WScript.Quit(0)' at where the End Function is, this
thing runs like a 'scalded cat' as a .VBS file from the command prompt.

but again, I'd prefer to keep this as a DTS package and generate the isql as
a command line v.s. and input file.

Rob

Hi,
iam not 100% sure if I follow your code correctly. I kind of guess that the
problem
you are encountering is Wscript object required error message.
From your code , it looks like
'Set WshShell = WScript.CreateObject("WScript.Shell")
is commented out. Is that right?
If it is not commented , you may want to change it to :

Set WshShell = CreateObject("WScript.Shell")

Iam not using Wscript object in the above line. I had similar issue with
opening
and closing an EXCEL file using Wscript object. ONce I removed Wscript object,
it worked perfectly fine inside DTS.

good luck
Kanan



"Rob" wrote: