Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Copy txt file into excel vbscript

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Copy txt file into excel vbscript
# 1  
Old 08-03-2018
Copy txt file into excel vbscript
Hi everybody,

I am working on Windows 10 and using cygwin.
I have a vbscript as follows:

Code:
Set objExcel = CreateObject("Excel.Application")  
objExcel.Visible = True  
Set wb1 = objExcel.Workbooks.open("\\files\share\path\file1.xlsx") 
wb1.Worksheets("Sheet1").Range("A1:CR89").Clear
Set wb2 = objExcel.Workbooks.open("\\files\share\path\test.txt") 
wb2.Worksheets("test").usedrange.copy
wb1.Worksheets("Sheet1").usedrange.pastespecial
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "cmd.exe /c echo. >NUL  | clip", 0, True
wb2.Close
wb1.Save

I want at first to open file1.xlsx, delete cells in file1.xlsx, copy the content of the test.txt file inside file1.xlsx, and finally save and close file1.xlsx.

This script works, but I was wondering if there is a way to do the same task without having to open the test.txt file with excel? Something which would temporarily copy the content of test.txt without having this file opened.

Thanks in advance for any help.

Supernono06
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Copy the content from txt file and create a html file

I have a txt file with a list of error messages in a xml tag format, and each error message is separated with a identifier(endresult).Need to split that and copy and create a new html file.Error message has some special character. how to escape the special character and insert my data into the... (7 Replies)
Discussion started by: DevAakash
7 Replies

2. Shell Programming and Scripting

Copy data to CSV file from txt output

Hi Everyone , Below is output from a TL1 , I want just the NE Name: and beside that the Temperature and the voltages in a csv file , Is this possible? > act-user:AB1S2a:ArshadFO:493::**********; AB1S2a 2016-02-07 10:13:24 M 493 COMPLD "ArshadFO:2016-02-07 10-04-55,0" ;... (11 Replies)
Discussion started by: adgjmpt
11 Replies

3. Windows & DOS: Issues & Discussions

How to copy a file with long records ,i.e spanning to 2 or 3 lines from UNIX to excel?

Hi Experts, I have a Unix csv file which has long records ie the record length is more than 80 so it goes to the next line.So when its in unix though it spans to two or three lines it still counts it as one record. But what is happening is for the records that are long when i copy it into excel i... (0 Replies)
Discussion started by: 100bees
0 Replies

4. UNIX for Dummies Questions & Answers

How to copy a file opened in UNIX to word or txt?

Hi I would like to know how to copy a file opened in unix editor like Nedit or vi editor to a word document Since I am newbie I want to make sure I am not really copying onto real subdirectories I tried to right click and copy the entire .sql file or any other file opened.. Nedit doesn't... (3 Replies)
Discussion started by: swathi123
3 Replies

5. Shell Programming and Scripting

Copy Data from CSV file to Excel Sheet using Perl

Hi All, Firstly I will like to wish A Happy New Year to all. Now my issue is I have one csv file say(data.csv) and one excel file say(result.xls) The result.xls contains two sheet name Sheet1 and Sheet2, Now What I am trying to do is to First I want to delete that data of Sheet2 if present any,... (6 Replies)
Discussion started by: adisky123
6 Replies

6. Shell Programming and Scripting

Converting txt file to Excel file

Hi All, I have a text file with below content. TradeDate Name SecurityMnc ReasonDesc ======================================= 20120501 Robin ABC FO System Defect 20120502 Robin ABC FO System Defect I would want this in an excel file in 4 columns,... (3 Replies)
Discussion started by: robinbannis
3 Replies

7. Shell Programming and Scripting

Help with selecting column with awk for a txt file generated by excel

I am new to scripting/programming, so I apologize for any novice questions. I have a tab delimited text file that was saved from excel xls file. I am trying to select only the third column using awk command. My command line is as below: cat test.txt | awk '{print $3}' However, above... (8 Replies)
Discussion started by: SangLad
8 Replies

8. Windows & DOS: Issues & Discussions

Split Data in one column into 2 column in Excel using DOS or VBScript

Hi I have some data in my Excel File.However all the data is in one single column.I want to split it into two columns. Current Data: 1,Hi Everyone,I am 7,New To Dos,And 17,VB Script,i could 110,have tried this thing 1800,in UNIX Desired Output CELL1|CELL 2 1 |Hi... (3 Replies)
Discussion started by: dashing201
3 Replies

9. UNIX for Dummies Questions & Answers

Save Excel file as .txt in UNIX format

I have some files created in Excel that have to be saved as .txt files in order to load them into our accounting system. I can save the files as .txt files through Excel, but I then have to open them in TextPad and do a save as to change the Format from PC to UNIX. Is there a way to skip this step... (2 Replies)
Discussion started by: jroyalty
2 Replies

10. Shell Programming and Scripting

how to copy data to to excel file

Hi, Can any one tell me how to copy data using shell script to a excel file from text file to other columns of excel file,leaving first column unaffected i.e it should not overwrite data in first column. Say my text file data is: 15-dec-2008 15-dec-2009 16-dec-2008 16-dec-2009 ... (7 Replies)
Discussion started by: tucs_123
7 Replies
Login or Register to Ask a Question

LEARN ABOUT DEBIAN

excel::template::container::format

Excel::Template::Container::Format(3pm) 		User Contributed Perl Documentation		   Excel::Template::Container::Format(3pm)

NAME

       Excel::Template::Container::Format - Excel::Template::Container::Format

PURPOSE

       To format all children according to the parameters

NODE NAME

       FORMAT

INHERITANCE

       Excel::Template::Container

ATTRIBUTES

       Boolean attributes should be set to 1, 0, true, or false.

       Color values can be the color name or the color index. See "COLOURS IN EXCEL" in Spreadsheet::WriteExcel

       o   align

	   Set to either left, center, right, fill, or justify. Default is left.  See also valign.

       o   bg_color

	   Set to a color value. Default is none.

       o   bold

	   This will set bold to on or off, depending on the boolean value.

       o   border

	   Set the border for all for edges of a cell. Also see bottom, top, left, and right.  Valid values are 0 - 7.

	   See "set_border()" in Spreadsheet::WriteExcel

       o   border_color

	   Sets the color value for the border. See also border, top_color, bottom_color, left_color and right_color.

       o   bottom

	   See border.

       o   bottom_color

	   See border_color

       o   color

	   This will set the color of the text, depending on color value. Default is black.

       o   fg_color

	   Set to a color value. This color will be used in foreground of some patterns. See color to change the color of text. Also see bg_color
	   and pattern.

       o   font

	   This will sent the font face. Default is Arial.

       o   font_outline

	   This will set font_outline to on or off, depending on the boolean value. (q.v.  OUTLINE tag)

       o   font_shadow

	   This will set font_shadow to on or off, depending on the boolean value. (q.v.  SHADOW tag). This only applies to Excel for Macintosh.

       o   font_strikeout

	   This will set font_strikeout to on or off, depending on the boolean value. (q.v.  STRIKEOUT tag)

       o   hidden

	   This will set whether the cell is hidden to on or off, depending on the boolean value.

       o   indent

	   Set the indentation level for a cell. Positive integers are allowed.

       o   italic

	   This will set italic to on or off, depending on the boolean value. (q.v. ITALIC tag)

       o   left

	   See border.

       o   left_color

	   See border_color.

       o   num_format

	   Set to the index of one of Excel's built-in number formats. See "set_num_format()" in Spreadsheet::WriteExcel

       o   pattern

	   Set to an integer, 0 - 18. Sets the background fill pattern of a ell. Default is 1, solid.

       o   right

	   See border.

       o   right_color

	   See border color.

       o   rotation

	   Set the rotation of the text in a cell. The rotation can be any angle in the range -90 to 90 degrees.  The angle 270 is also supported.
	   This indicates text where the letters run from top to bottom.

       o   shrink

	   A boolean value. If true, text will shrink to fit a cell.

       o   size

	   This will set the size of the font. Default is 10. Unless a row height is specifically set, the row will grow taller as necessary.

       o   text_justlast

	   A boolean value to justify the last line. Only applies to Far Eastern versions of Excel.

       o   text_wrap

	   A boolean value. When set to true, text will wrap in a cell instead of crossing over into empty cells. If the row height is not set,
	   the row will grow taller to accommodate the wrapping text.

       o   top

	   See border.

       o   top_color

	   See border_color

       o   valign

	   Set to top, vcenter, bottom, or vjustify. Default is vcenter. See also align.

CHILDREN

       None

EFFECTS

       None

DEPENDENCIES

       None

USAGE

	 <format bold="1">
	   ... Children here
	 </format>

       In the above example, the children will be displayed (if they are displaying elements) in a bold format. All other formatting will remain
       the same and the "bold"-ness will end at the end tag.

AUTHOR

       Rob Kinyon (rob.kinyon@gmail.com)

SEE ALSO

       BOLD, HIDDEN, ITALIC, OUTLINE, SHADOW, STRIKEOUT

perl v5.14.2							    2012-04-29				   Excel::Template::Container::Format(3pm)