Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I import a .dat file into excel using VB scripting?
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I import a .dat file into excel using VB scripting?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I import a .dat file into excel using VB scripting?
Try adding this line:
Set ExcelSheet = excelApp.Workbooks.Open(FileName:=fileVariable, ReadOnly:=False)' excelApp.UserControl = True -- Regards, Tom Ogilvy wrote in message oups.com... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I import text file of cash flow to excel file then use formula | Excel Discussion (Misc queries) | |||
import file so that it is a actual csv file, no excel cell version | New Users to Excel | |||
How come I can't import an .svc file(Open Office) to Excel file? | Excel Discussion (Misc queries) | |||
Software of scripting to grab data from MS word file and fill in Excel fields? | Excel Programming | |||
Import text file into excel with preset file layout, delimeters VBA | Excel Programming |