| |
TNO
Posts: 1397
Score: 16
Joined: 12/18/2004
From: thenewobjective.com
Status: offline
|
Greetings, presently I have a database application I've developed with an .hta. This program creates a temporary delimited text file which I export into EXCEL using the following script:
Option Explicit
Const vbNormal = 1 ' window style
DIM objXL, objWb, objR, objTab ' Excel object variables
DIM Title, Text, tmp, i, j, file, name
Title = "import file test"
' here you may set the name of the file to be imported
file = "Temp.txt" ' must be located in the script folder
' create an Excel object reference
Set objXL = WScript.CreateObject ("Excel.Application")
' set the Excel window properties (not absolutely necessary)
objXL.Visible = true ' show window
' Create new Workbook (needed for import the CSV file=
Set objWb = objXl.WorkBooks.Add
' Get the first loaded worksheet object of the current workbook
Set objWb = objXL.ActiveWorkBook.WorkSheets(1)
objWb.Activate ' not absolutely necessary (for CSV)
' Now invoke the import wizard
Set objTab = objWb.QueryTables.Add ("TEXT;"+GetPath + file, objWb.Range("A1"))
' here comes the mumbo jumbo to set all the properties for the wizard
' Oh Microsoft, how do I wish to has a With feature or a possibility to
' pass named arguments to methods ....
objTab.Name = "Names"
objTab.FieldNames = True
objTab.RowNumbers = False
objTab.FillAdjacentFormulas = False
objTab.PreserveFormatting = True
objTab.RefreshOnFileOpen = False
objTab.RefreshStyle = 1 'xlInsertDeleteCells
objTab.SavePassword = False
objTab.SaveData = True
objTab.AdjustColumnWidth = True
objTab.RefreshPeriod = 0
objTab.TextFilePromptOnRefresh = False
objTab.TextFilePlatform = 2 'xlWindows
objTab.TextFileStartRow = 1
objTab.TextFileParseType = 1 'xlDelimited
objTab.TextFileTextQualifier = -4142 ' xlTextQualifierNone
objTab.TextFileConsecutiveDelimiter = False
objTab.TextFileTabDelimiter = True ' ### my delimiters
objTab.TextFileSemicolonDelimiter = True
objTab.TextFileCommaDelimiter = False
objTab.TextFileSpaceDelimiter = False
objTab.TextFileColumnDataTypes = Array(1, 1)
objTab.Refresh False
WScript.Echo "Excel Import Complete"
' demonstrate how to read the column header values
Text = "Worksheet " + objWb.name + vbCRLF
Text = Text + "Column titles" + vbCRLF
Text = Text + CStr(objWb.Cells(1, 1).Value) + vbTab
Text = Text + CStr(objWb.Cells(1, 2).Value) + vbCRLF
' show some cell values (using the "hard coded method")
Text = Text + CStr(objWb.Cells(2, 1).Value) + vbTab
Text = Text + CStr(objWb.Cells(2, 2).Value) + vbCRLF
Text = Text + CStr(objWb.Cells(3, 1).Value) + vbTab
Text = Text + CStr(objWb.Cells(3, 2).Value) + vbCRLF
Text = Text + CStr(objWb.Cells(4, 1).Value) + vbTab
Text = Text + CStr(objWb.Cells(4, 2).Value) + vbCRLF
' I like to prevent the warning message about the unsaved data
' during closing Excel
objXL.DisplayAlerts = False ' prevent all message boxes
Set objXL = Nothing
WScript.Quit()
'##########################
Function GetPath
' Retrieve the script path
DIM path
path = WScript.ScriptFullName ' Script name
GetPath = Left(path, InstrRev(path, "\"))
End Function
Presently this is run in a .vbs file called from the .hta
How would I make this compatible for use within my hta?
I've already tried replacing WScript.CreateObject() with CreateObject()
_____________________________
To iterate is human, to recurse divine. -- L. Peter Deutsch
|
|
Printable Version
Excel vbs with HTA - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread