Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to lmport a text file and save it as an excel file using vb s.
I am trying to import a text file and save it as a normal excel file in VB
Script. I first recorded a macro that has steps to open the text file parse it into columns and save it into a excel 2003 file. However when I copy and past the macro with changes necessary for the VB script , vb script either fails in the save-as step or brings up an interactive diaglog box for saving. What is the way todo with out bringing up the diaglog box. Below is my simple code.. Dim oXcel Set oxcel = CreateObject("excel.Application") oxcel.workbooks.add oxcel.workbooks.OpenText "MyFile.txt" oxcel.Rows("1:1").Select oxcel.Selection.Font.Bold = True oxcel.Columns("A:J").Select oxcel.Range("J1").Activate oxcel.Selection.Columns.AutoFit ON Error Resume Next oxcel.workbooks.SaveAs "MyFile.xls",4 if err.number < 0 then wscript.echo "SaveAs error:",err.description end if On Error goto 0 oxcel.quit Set oxcel = Nothing ===== thanks Newbie Scriptor |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to lmport a text file and save it as an excel file using vb s.
I'm a Newbie too,
but I think you need to run text-to-columns after you open the file. When import a text file it all gets dumped into the first column. Then you can format, autofit, and save. See the examples below (Text-to-columns): Workbooks.Open FileName:=Fname 'Method I Set mySheet = ActiveSheet myShtName = ActiveSheet.Name R = mySheet.UsedRange.Rows.Count mySheet.Range(Cells(1, 1), Cells(R, 1)).Select Application.Dialogs(xlDialogTextToColumns).Show Columns.AutoFit 'Method II Application.Dialogs(xlDialogOpen).Show Arg1:=Fname Set mySheet = ActiveSheet myShtName = ActiveSheet.Name R = mySheet.UsedRange.Rows.Count Columns.AutoFit See the examples below (Saving file as 'xls'): In VBA the code for saving the file would look like this, where Fname is a string; I.E: Fname = ThisWorkbook.Path & Application.PathSeparator & "MyFile.xls": Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=Fname, FileFormat:=xlNormal Application.DisplayAlerts = True HTH, Lonnie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to lmport a text file and save it as an excel file using v
Lonnie,
Thanks for your reply. I guess your code works only after opening up an excel instance and importing and selecting the text into a column. However what I am trying to do is in a batch mode with no manual interaction, create a new excel file, read into it a text file (which is tab delimited , so opentext method should automatically be able to handle the parsing into multiple columns) and then save it as a normal formatted Excel file. My sample code does correctly upto formatting the imported text. However whne trying to save it as an Excel file it brings up the "SaveAs" dialog box. I am running the script using "cscript.exe" not as a macro from within Excel workbook. Thanks and hope you can help me further Newbie scriptor "Lonnie M." wrote: I'm a Newbie too, but I think you need to run text-to-columns after you open the file. When import a text file it all gets dumped into the first column. Then you can format, autofit, and save. See the examples below (Text-to-columns): Workbooks.Open FileName:=Fname 'Method I Set mySheet = ActiveSheet myShtName = ActiveSheet.Name R = mySheet.UsedRange.Rows.Count mySheet.Range(Cells(1, 1), Cells(R, 1)).Select Application.Dialogs(xlDialogTextToColumns).Show Columns.AutoFit 'Method II Application.Dialogs(xlDialogOpen).Show Arg1:=Fname Set mySheet = ActiveSheet myShtName = ActiveSheet.Name R = mySheet.UsedRange.Rows.Count Columns.AutoFit See the examples below (Saving file as 'xls'): In VBA the code for saving the file would look like this, where Fname is a string; I.E: Fname = ThisWorkbook.Path & Application.PathSeparator & "MyFile.xls": Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=Fname, FileFormat:=xlNormal Application.DisplayAlerts = True HTH, Lonnie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to lmport a text file and save it as an excel file using v
Newbie,
The following example is one that I used for bringing in seven columns of data with no wizard interaction, and could easily be looped to do a batch of text files. 'Batch Method ' The Array(X,X) in field info represents the column and data type 'Where X represents the column, Y represents the data type ' 1 = General; 2 = Text; 3 = Date (MMDDYY) Workbooks.OpenText FileName:=Fname, Origin _ :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False _ , Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 2), Array(5, 1), _ Array(6, 3), Array(7, 3)) Columns.AutoFit Saving the file: I would think that you would have to turn off display alerts and use the SaveAs function, passing it the directory and file name. See example below: xlApp.DisplayAlerts = False xlApp.ActiveWorkbook.SaveAs Filename:=FileDirectoryName, FileFormat:=xlNormal xlApp.DisplayAlerts = True HTH--Lonnie M. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to lmport a text file and save it as an excel file using v
Newbie,
The following example is one that I used for bringing in seven columns of data with no wizard interaction, and could easily be looped to do a batch of text files. 'Batch Method ' The Array(X,Y) in field info represents the column and data type 'Where X represents the column, Y represents the data type ' 1 = General; 2 = Text; 3 = Date (MMDDYY) Workbooks.OpenText FileName:=Fname, Origin _ :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False _ , Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 2), Array(5, 1), _ Array(6, 3), Array(7, 3)) Columns.AutoFit Saving the file: I would think that you would have to turn off display alerts and use the SaveAs function, passing it the directory and file name. See example below: xlApp.DisplayAlerts = False xlApp.ActiveWorkbook.SaveAs Filename:=FileDirectoryName, FileFormat:=xlNormal xlApp.DisplayAlerts = True HTH-Lonnie M. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to lmport a text file and save it as an excel file using v
(Sorry, if there are multiple posts, the server says that it is not
able to post, and try again) Newbie, The following example is one that I used for bringing in seven columns of data with no wizard interaction, and could easily be looped to do a batch of text files. 'Batch Method ' The Array(X,Y) in field info represents the column and data type 'Where X represents the column, Y represents the data type ' 1 = General; 2 = Text; 3 = Date (MMDDYY) Workbooks.OpenText FileName:=Fname, Origin _ :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False _ , Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 2), Array(5, 1), _ Array(6, 3), Array(7, 3)) Columns.AutoFit Saving the file: I would think that you would have to turn off display alerts and use the SaveAs function, passing it the directory and file name. See example below: xlApp.DisplayAlerts = False xlApp.ActiveWorkbook.SaveAs Filename:=FileDirectoryName, FileFormat:=xlNormal xlApp.DisplayAlerts = True The following are examples that I found in other groups; the source's name precedes the example: Michael Bednarek Set myApp = WScript.CreateObject("Excel.Application") Set myBook = myApp.WorkBooks.Open("d:\path\workbook.xls") myApp.Run("MyMacro") myBook.Save ' if you want/need to Set myBook=nothing myApp.Quit Set myApp = Nothing Anthony Borla } function convert(fromFile, fromDelimiter, fromPlatformCRLF, fromPlatform, toXLSFile) { // Open the desired file using specified conversion filter Excel.Workbooks.Open(fromFile, 0, true, fromDelimiter, "", "", false, fromPlatformCRLF, "", false, false, fromPlatform, false); // Save as desired file type using specified conversion filter Excel.ActiveSheet.SaveAs(toXLSFile, xlWorkbookNormal, "", "", false, false); // Close Document Excel.Workbooks.Close(); HTH-Lonnie M. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to lmport a text file and save it as an excel file using v
Newbie,
The following example is one that I used for bringing in seven columns of data with no wizard interaction, and could easily be looped to do a batch of text files. 'Batch Method ' The Array(X,Y) in field info represents the column and data type 'Where X represents the column, Y represents the data type ' 1 = General; 2 = Text; 3 = Date (MMDDYY) Workbooks.OpenText FileName:=Fname, Origin _ :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False _ , Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 2), Array(5, 1), _ Array(6, 3), Array(7, 3)) Columns.AutoFit Saving the file: I would think that you would have to turn off display alerts and use the SaveAs function, passing it the directory and file name. See example below: xlApp.DisplayAlerts = False xlApp.ActiveWorkbook.SaveAs Filename:=FileDirectoryName, FileFormat:=xlNormal xlApp.DisplayAlerts = True The following are VBS examples that I found in other groups; the source's name precedes the example: Michael Bednarek Set myApp = WScript.CreateObject("Excel.Application") Set myBook = myApp.WorkBooks.Open("d:\path\workbook.xls") myApp.Run("MyMacro") myBook.Save ' if you want/need to Set myBook=nothing myApp.Quit Set myApp = Nothing Anthony Borla } function convert(fromFile, fromDelimiter, fromPlatformCRLF, fromPlatform, toXLSFile) { // Open the desired file using specified conversion filter Excel.Workbooks.Open(fromFile, 0, true, fromDelimiter, "", "", false, fromPlatformCRLF, "", false, false, fromPlatform, false); // Save as desired file type using specified conversion filter Excel.ActiveSheet.SaveAs(toXLSFile, xlWorkbookNormal, "", "", false, false); // Close Document Excel.Workbooks.Close(); HTH-Lonnie M. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to lmport a text file and save it as an excel file using v
Newbie,
The following example is one that I used for bringing in seven columns of data with no wizard interaction, and could easily be looped to do a batch of text files. 'Batch Method ' The Array(X,Y) in field info represents the column and data type 'Where X represents the column, Y represents the data type ' 1 = General; 2 = Text; 3 = Date (MMDDYY) Workbooks.OpenText FileName:=Fname, Origin _ :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False _ , Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 2), Array(5, 1), _ Array(6, 3), Array(7, 3)) Columns.AutoFit Saving the file: I would think that you would have to turn off display alerts and use the SaveAs function, passing it the directory and file name. See example below: xlApp.DisplayAlerts = False xlApp.ActiveWorkbook.SaveAs Filename:=FileDirectoryName, FileFormat:=xlNormal xlApp.DisplayAlerts = True The following are VBS examples that I found in other groups; the source's name precedes the example: Michael Bednarek Set myApp = WScript.CreateObject("Excel.Application") Set myBook = myApp.WorkBooks.Open("d:\path\workbook.xls") myApp.Run("MyMacro") myBook.Save ' if you want/need to Set myBook=nothing myApp.Quit Set myApp = Nothing Anthony Borla } function convert(fromFile, fromDelimiter, fromPlatformCRLF, fromPlatform, toXLSFile) { // Open the desired file using specified conversion filter Excel.Workbooks.Open(fromFile, 0, true, fromDelimiter, "", "", false, fromPlatformCRLF, "", false, false, fromPlatform, false); // Save as desired file type using specified conversion filter Excel.ActiveSheet.SaveAs(toXLSFile, xlWorkbookNormal, "", "", false, false); // Close Document Excel.Workbooks.Close(); HTH-Lonnie M. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to lmport a text file and save it as an excel file using v
Hi,
I realize that I forgot to change the 'X' to a 'Y' in my example: ' The Array(X,Y) in field info represents the column and data type 'Where X represents the column, Y represents the data type Sorry about that--Lonnie M. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I save an excel file as a file with undelimited text | Excel Discussion (Misc queries) | |||
How do you save an excel file to be read as IBM-type text file ? | Excel Worksheet Functions | |||
How can I save a file as a comma-delimited text file in Excel? | Excel Discussion (Misc queries) | |||
Excel VBA - open text file, replace text, save file? | Excel Programming | |||
Excel VBA to save .xls file as UTF-8 text file | Excel Programming |