thank you in advance.
This code is a hodge podge of code I borrowed/rearranged/wrote. If you
have suggestions on ways to make it shorter, cleaner, better, please add
your comments.
I'm attempting to query a webpage, export the page to an Excel
spreadsheet, and save. The code uses three input files for now (to
account for multiple backup servers and various dns naming conventions
and the list of servers to check). Ultimately, the information will be
pulled directly out of the SQL database, but being impatient and wanting
to learn more about VB, VBA, and VBScript, I've been tinkering.
The code works to the point where it attempts the web query (in other
words, reads the text files into arrays, opens Excel, names the active
sheet). However, I get a "Type mismatch" error for line 146 character
26 at that point. That line is where Excel establishes the connection
to the web page. It appears to be telling me that my "expression.Add"
line has the wrong modifiers. Am I reading the error incorrectly?
Is the format of the query incorrect? What is the correct "type?"
<script begin>
Option Explicit
'---------------------------------------------------
'original shell used to:
'use 2 input files containing computer names
'compare the lists and output to file the results
'XXXX XXXX 2003
'Originally used to check and create computer groups
'modified to take file input and determine if backups
'are good. input1=list of servers, input2=list of
'backup servers, input3=various dns string endings
'for different corporate intranets within company
'modified by XXX XXX 07/07/03
'---------------------------------------------------
'Set up Initial key Variables-----------------------
redim olist1(1)
redim olist2(1)
redim olist3(1)
dim thefile1,thefile2,thefile3,othefile1,othefile2,othefile3
dim opos,opath1,opath2,opath3
dim oFS,oinputfile1,oinputfile2,oinputfile3,oStr1,oStr2,oStr3
dim linecount1,linecount2,linecount3,oinline1,oinline2,oinline3
dim x,y,z,ExcelSheet,urlStr,objHTTP,ExcelBook
dim Connection,Destination,Range
'---------------------------------------------------
'Argument Capture
'---------------------------------------------------
if wscript.arguments.count=0 then
thefile1=inputbox("Enter the file name including path of the"&_
" file with your servers listed in it : "&vbcrlf&"Example : My"&_
" input file.txt","Input file Name","input1.txt")
othefile1=thefile1
if thefile1="" then
wscript.quit
end if
else
'Use the FileName supplied as an argument
thefile1=wscript.arguments(0)
opos=instrrev(thefile1,"\")
opath1=mid(thefile1,1,opos)
othefile1=mid(thefile1,opos+1,len(thefile1))
end if
if wscript.arguments.count=0 then
thefile2=inputbox("Enter the file name including path of the"&_
" file with the backup servers listed in it : "&vbcrlf&"Example :"&_
" Backup servers.txt","Input file Name","input2.txt")
othefile2=thefile2
if thefile2="" then
wscript.quit
end if
else
'Use the FileName supplied as an argument
thefile2=wscript.arguments(1)
opos=instrrev(thefile2,"\")
opath2=mid(thefile2,1,opos)
othefile2=mid(thefile2,opos+1,len(thefile2))
end if
if wscript.arguments.count=0 then
thefile3=inputbox("Enter the name of the DNS strings file (include
path) :"&vbcrlf&"Example : DNSStrings.txt","Input File
Name","input3.txt")
othefile3=thefile3
if thefile3="" then
wscript.Quit
end if
else 'Use the default FileName as the argument
thefile3=wscript.Arguments(2)
opos=instrrev(thefile3,"\")
opath3=mid(thefile3,1,opos)
othefile3=mid(thefile3,opos+1,len(thefile3))
end if
'---------------------------------------------------
'Open Files for reading and writing
'---------------------------------------------------
set oFS = CreateObject("scripting.filesystemobject")
if not ofs.fileexists(thefile1) and ofs.fileexists(thefile2) then
'File Not Found Exit Application
wscript.echo "Input Files Not Found"&vbcrlf&"END Program!"
wscript.quit
end if
'---------------------------------------------------
'Read input file into Array (olist1)
'---------------------------------------------------
set oInputFile1 = oFS.opentextfile(thefile1,1,False)
linecount1=0
Do while not oinputfile1.AtEndOfStream
oinline1=oinputfile1.Readline
if not oinline1="" then
if linecount1>ubound(olist1) then
redim preserve olist1(linecount1)
end if
olist1(linecount1)=oinline1
linecount1=linecount1+1
end if
loop
oinputfile1.close
set oInputFile2 = oFS.opentextfile(thefile2,1,False)
linecount2=0
Do while not oinputfile2.AtEndOfStream
oinline2=oinputfile2.Readline
if not oinline2="" then
if linecount2>ubound(olist2) then
redim preserve olist2(linecount2)
end if
olist2(linecount2)=oinline2
linecount2=linecount2+1
end if
loop
oinputfile2.close
set oInputFile3 = oFS.opentextfile(thefile3,1,False)
linecount3=0
Do while not oinputfile3.AtEndOfStream
oinline3=oinputfile3.Readline
if not oinline3="" then
if linecount3>ubound(olist3) then
redim preserve olist3(linecount3)
end if
olist3(linecount3)=oinline3
linecount3=linecount3+1
end if
loop
oinputfile3.close
'---------------------------------------------------
'Main Body
'---------------------------------------------------
Set ExcelSheet=CreateObject("Excel.Application")
Set ExcelBook=ExcelSheet.Workbooks.Add
'"c:\scriptemp\bocada.xls",False,True,False
ExcelSheet.Visible = True
'ExcelSheet.Application.Workbooks.Add
ExcelBook.Worksheets(1).Activate
ExcelSheet.ActiveWorkbook.ActiveSheet.Name="Bocada Report"
for x=0 to ubound(olist1)
oStr1=olist1(x)
if not oStr1="" then
for y=0 to ubound(olist2)
oStr2=olist2(y)
if not oStr2="" then
for z=0 to ubound(olist3)
oStr3=olist3(z)
if not oStr3="" then
urlStr="http://servernamexyz/reports/"&oStr2&"-bdt_"&oStr1&"-bdt."&oStr
3&".html"
Set objHTTP = CreateObject("MSXML2.XMLHTTP")
Call objHTTP.Open("GET",urlStr,FALSE)
objHTTP.Send
if not len(objHTTP.ResponseText)<5000 then
ExcelSheet.ActiveWorkbook.Worksheets.Add
ExcelSheet.ActiveWorkbook.ActiveSheet.Name=oStr1&"."&oStr3
With
ExcelSheet.ActiveWorkbook.ActiveSheet.QueryTables.Add(Connection="URL;"&
urlStr,Destination=Range("A1"))
.Name = oStr2&"-bdt_"&oStr1&"-bdt."&oStr3
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "4,5"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery=False
End With
end if
end if
next
end if
next
end if
next
ExcelSheet.Application.Save "c:\scriptemp\bocada.xls"
ExcelSheet.Quit
outputfile.Close
'---------------------------------------------------
'Error Handling
'---------------------------------------------------
if not err.number=0 then
wscript.echo err.number&vbcrlf&err.description
end if
'End of Program-------------------------------------
wscript.echo "Process Complete"&vbcrlf&"End Program"
<script end>
--
Direct access to this group with http://web2news.com
http://web2news.com/?microsoft.public.vb.syntax