create object with wildcard.

[vsantoro]

currently using this code...

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
("C:\Tmp\default.xls")

want to use wildcard like this..


Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
("C:\Tmp\*.xls")

but does not work. Here is all the code..

Set IE = CreateObject("InternetExplorer.Application")

IE.Navigate "

http://Reports/PNET_RFI_ORACLE/"

IE.Visible = True
Wscript.Sleep 3000
IE.Document.All.Item("Button1").Click
Wscript.Sleep 3000

' create object
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
("C:\Tmp\*.xls")

'save the tmp xls file
objWorkbook.SaveAs "C:\RFI_XLS_Temp\test.xls"

'close xls
objExcel.Application.Quit

'open the saved xls file
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\RFI_XLS_Temp\test.xls")
Wscript.Sleep 2000


'Close Excel
objExcel.Application.Quit
objExcel.Quit

 

[jges]

What exactly are you hoping to accomplish with the wildcard? It appears to me that you are either:
1) trying to open multiple .xls files
or
2) trying to open a file with an unknown name.

If #1 is the case, it will fail because you can only assign one .xls file to the single workbook object. You would need to create multiple workbooks to open multiple files (1:1).

If #2 is the case, you can loop through the files in the folder of interest to determine the number of .xls files and their names. If/when you find one of interest you can then open it.

Also, as an observation; you will want to make the excel application visible if user interaction is required.

Here are a couple of FAQ's that may help:
faq329-7301
faq329-299

 

[vsantoro]

there will be only 1 xls in that folder, i know because im running code in the beginning to check that folder and delete all xls before new one is generated.

'delete any xls files in Tmp directory
Const DeleteReadOnly = True
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.DeleteFile("C:\Tmp\*.xls"), DeleteReadOnly




then i want to create object like this

create object
'Set objExcel = CreateObject("Excel.Application")
'Set objWorkbook = objExcel.Workbooks.Open _
' ("C:\Tmp\default.xls")


except want to use wildcard instead of exact path to file.
because file name could be default.xls, or default1.xls, or default2.xls

 

[vsantoro]

i can get the xls filename (code below), but dont know how to assign it to objworkbook


strFolder="C:\Tmp"
Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFSO.GetFolder(strFolder)
Set colFiles = objFolder.Files
'Loop
for Each objFile in colFiles
if lcase(right(objFile.name,3))="xls" then
wscript.echo objFile.Name
end if
Next

 

[jges]

Try this:

Set objWorkbook = objExcel.Workbooks.Open (objFSO.GetAbsolutePathName(objFile))

 

[mikrom]

something like this could work:

strFolder="C:\Tmp"
Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFSO.GetFolder(strFolder)
Set colFiles = objFolder.Files

set objExcel = CreateObject("Excel.Application")
'Loop 
for Each objFile in colFiles
  if lcase(right(objFile.name,3))="xls" then
     wscript.Echo objFile
     set objWorkbook = objExcel.Workbooks.Open(objFile, false, true)
     'process XLS-File
     '...
     objWorkbook.close 
     set objWorkBook = Nothing
  end if
Next

objExcel.quit
set objExcel = nothing