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?

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

Try adding this line:

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

excelApp.UserControl = True

--
Regards,
Tom Ogilvy


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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?
>