How do I import a .dat file into excel using VB scripting?

[seth]

I have a program that writes tab seperated values into a .dat file. If
I were to open this .dat file in excel using the import wizard, the
formatting is perfect because I already I have rows/columns set up
within the .dat file.

Basically, my goal is to have a VB script that runs at the end of my
program which will read from the .dat file and import that data into
excel, so I can use the math functions built-in to excel. Also, so
someone else could then use the program and overwrite that .dat file
with their own data to export to their own spreadsheet.


*creating objects
'Dim excelApp As Object'
'Dim ExcelSheet As Object'

*error handling
'On Error Resume Next'

*start up excel
'Set excelApp = CreateObject("Excel.Application")'
'excelApp.Application.Visible=True'


** open an xls (fileVariable contains the directory and a blank excel
document to use, I am not sure I need this or if there is a way to
create a 'fresh' xls to write the dat file to)

'Set ExcelSheet = excelApp.Workbooks.Open(FileName:=fileVariable,
ReadOnly:=False)'

~~~~~
Thats all I have so far. What I need to be able to do is basically
open the .dat file and put the data into that opened Excel file and
KEEP EXCEL OPEN so I can use the spreadsheet for Math. If I execute
what I currently have I see excel open (the blank document i think)
and close immediately.
Can anyone help out this newbie to VB?

 

[Greg Glynn]

You need to do comething like this:

MyFilename = Application.GetOpenFilename(filefilter:="Text files,
*.dat", MultiSelect:=False)

If MyFilename <> "" And MyFilename <> "False" Then
Workbooks.OpenText Filename:=MyFilename, Origin:=xlWindows,
StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlNone,
ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(1, 1)
End if

 

[Tom Ogilvy]

Try adding this line:

Set ExcelSheet = excelApp.Workbooks.Open(FileName:=fileVariable,
ReadOnly:=False)'

excelApp.UserControl = True