vba not opening file 1

[xxentric]

I'm not sure whats wrong with this code.. but its not opening a file, when i run it, i do not get any errors, but nothing happens... i have checked the file names to make sure it matches the code, but still nothing

Option Explicit

Const ForReading = 1
Const ForWriting = 2

Dim objFSO, objFile, objHTMLFile , objShell
Dim strLine, strSearchFile, strPath, strLogFile, strFileItemPath

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")

strSearchFile = "F:\Downloads\Facetcoat log files WORK\SearchString.txt" ' Set the Search File Path
strPath = "F:\Downloads\Facetcoat log files WORK\Network Drive Folder" ' A Path to a Folder where you want to look in
strLogFile = "F:\Downloads\Facetcoat log files WORK\Results.html"

If objFSO.FileExists(strSearchFile) Then
  Set objFile = objFSO.OpenTextFile(strSearchFile,ForReading) ' Open the File to read from

' if you want to log this activity then include the next line
'  Set objHTMLFile = objFSO.OpenTextFile(strLogFile, ForWriting, true) ' Create/overwrite the Log File
 
  Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine ' read file
    if lcase(objFSO.GetExtensionName(strLine)) = "xls" then
      strFileItemPath = strPath & "\" & strLine
      if objFSO.FileExists(strFileItemPath) then ' checks to see if file is present
         objShell.Run Chr(34) & strFileItemPath & Chr(34), 1, False   ' starts the file and does NOT wait to continue

'        if you want to log this activity then include the next line (remove comment character)
'        objHTMLFile.WriteLine "<P><a href=""" & strFileItemPath & """>" & strFileItemPath & "</a></P>"

      end if
    end if
  Loop
  objFile.Close
  Set objFile = Nothing
Else
  WScript.Quit
End If

 

[mrmovie]

put some logging in, wscript.echo "found file will try and run " & strFileItemPath etc etc and then you will see waht your script is actually or not as the case may be.
also you should consider trapping the return code from your .Run methods
intReturn = 666
intReturn = WshShell.Run(m,,,,)
Wscript.Echo "return code from tryin to run " & strFileItemPath & " = " & CStr(intReturn)

 

[xxentric]

.. haha umm well i wish i could have listed myself as super newbie to scripting ... but they didnt have that... could you tell me where to put it that code in there?

 

[tsuji]

You have to check the "file association" of xls file (via clicking through the explorer->tools->folder options->...). Check its default action. The script is completely relying on that setting to work. Otherwise, you can insert the application (excel.exe or any other application that can open .xls file such as open office scalc.exe etc) itself and have the file (.xls) as its argument.

 

[mrmovie]

with regards the logging:

Option Explicit

Const ForReading = 1
Const ForWriting = 2

Dim objFSO, objFile, objHTMLFile , objShell
Dim strLine, strSearchFile, strPath, strLogFile, strFileItemPath

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")

strSearchFile = "F:\Downloads\Facetcoat log files WORK\SearchString.txt" ' Set the Search File Path
strPath = "F:\Downloads\Facetcoat log files WORK\Network Drive Folder" ' A Path to a Folder where you want to look in
strLogFile = "F:\Downloads\Facetcoat log files WORK\Results.html"

If objFSO.FileExists(strSearchFile) Then
Wscript.Echo "found " & strSearchFile
Set objFile = objFSO.OpenTextFile(strSearchFile,ForReading) ' Open the File to read from

' if you want to log this activity then include the next line
' Set objHTMLFile = objFSO.OpenTextFile(strLogFile, ForWriting, true) ' Create/overwrite the Log File
Wscript.Echo "have opened " & strSearchFile
Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine ' read file
Wscript.Echo "a line = " & strLine
if lcase(objFSO.GetExtensionName(strLine)) = "xls" then
Wscript.Echo vbTab & "this line represents an xls"
strFileItemPath = strPath & "\" & strLine
Wscript.Echo vbTab & "strFileItemPath = " & strFileItemPath
if objFSO.FileExists(strFileItemPath) then ' checks to see if file is present
Wscript.Echo vbTab & "found " & strFileItemPath
objShell.Run Chr(34) & strFileItemPath & Chr(34), 1, False ' starts the file and does NOT wait to continue

' if you want to log this activity then include the next line (remove comment character)
' objHTMLFile.WriteLine "<P><a href=""" & strFileItemPath & """>" & strFileItemPath & "</a></P>"

end if
end if
Loop
objFile.Close
Set objFile = Nothing
Else
WScript.Quit
End If

etc etc etc

 

[xxentric]

Ah, Thanks for the help! I'll try this

 

[mrmovie]

i was going to state what tsuji has pointed out but thought that was too much to the point ;-) am sure you will find, as tsuji states, its the lack of an association or even a lack of excel.exe or excel.exe location not being in the %PATH%

 

[xxentric]

oh, believe me... nothing is too much to the point for me right now. super newbie here, like... before even opening the first book on scripting kind of newbie. but im trying

 

[mrmovie]

buy a book, i find them a great help, 'in 21 days' or something like that is good place to start.

 

[xxentric]

I am gonna be on my way to get a book in an hour or so probably. I just didnt have enough time to learn it before this needs to be completed, which sucks

ok, so i read up on this (excel.exe) you spoke of, and i came up with this which of course still isnt working for me *bangs head on desk*, it opens the txt file reads the string, then... never opens the file

Option Explicit

Const ForReading = 1
Const ForWriting = 2

Dim objFSO, objFile, objHTMLFile , objShell, xl
Dim strLine, strSearchFile, strPath, strLogFile, strFileItemPath

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")
Set xl = CreateObject("Excel.application")

strSearchFile = "F:\Downloads\Facetcoat log files WORK\SearchString.txt" ' Set the Search File Path
strPath = "F:\Downloads\Facetcoat log files WORK\Network Drive Folder" ' A Path to a Folder where you want to look in
strLogFile = "F:\Downloads\Facetcoat log files WORK\Results.html"

If objFSO.FileExists(strSearchFile) Then
  Wscript.Echo "found " & strSearchFile
  Set objFile = objFSO.OpenTextFile(strSearchFile,ForReading) ' Open the File to read from

' if you want to log this activity then include the next line
'  Set objHTMLFile = objFSO.OpenTextFile(strLogFile, ForWriting, true) ' Create/overwrite the Log File
  Wscript.Echo "have opened " & strSearchFile
 Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine ' read file
      strFileItemPath = strPath & "\" & strLine & ".xls"
      if objFSO.FileExists(strFileItemPath) then ' checks to see if file is present
        xl.Application.Workbooks.Open chr(34) & strFileItemPath & Chr(34), 1, False   ' starts the file and does NOT wait to continue
xl.Application.Visible = True

'        if you want to log this activity then include the next line (remove comment character)
'        objHTMLFile.WriteLine "<P><a href=""" & strFileItemPath & """>" & strFileItemPath & "</a></P>"
    end if
  Loop
Set xl = Nothing
Else
  WScript.Quit
End If

 

[xxentric]

hmm :/ it doesnt seem like i need to do any sort of file association or insert application (excel. the script below opens the file fine

Set objShell = CreateObject("WScript.Shell")
objShell.Run """.\Eddy Run Log.xls"""

 

[xxentric]

Ah finally got it, works well, thanks for the help!

working code

Option Explicit

Const ForReading = 1
Const ForWriting = 2

Dim objFSO, objFile, objExcel, objWorkBook
Dim strLine, strSearchFile, strPath, strLogFile, strFileItemPath

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
objExcel.visible = true


strSearchFile = "C:\Users\FAM7\Desktop\Facetcoat log files\SearchString.txt" ' Set the Search File Path
strPath = "C:\Users\FAM7\Desktop\Facetcoat log files\Network Drive Folder" ' A Path to a Folder where you want to look in
'strLogFile = "F:\Downloads\Facetcoat log files WORK\Results.html"

If objFSO.FileExists(strSearchFile) Then
  Set objFile = objFSO.OpenTextFile(strSearchFile,ForReading) ' Open the File to read from


' if you want to log this activity then include the next line
' Set objHTMLFile = objFSO.OpenTextFile(strLogFile, ForWriting, true) ' Create/overwrite the Log File


 Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine ' read file
      strFileItemPath = strPath & "\" & strLine & ".xls"
	set objWorkbook = objExcel.Workbooks.Open(strFileItemPath)


' if you want to log this activity then include the next line (remove comment character)
' objHTMLFile.WriteLine "<P><a href=""" & strFileItemPath & """>" & strFileItemPath & "</a></P>"

  Loop
  objFile.Close
  Set objFile = Nothing
Else
End If
  WScript.Quit
[\code]