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

spreadsheet::xlsx

Spreadsheet::XLSX(3pm)					User Contributed Perl Documentation				    Spreadsheet::XLSX(3pm)

NAME

       Spreadsheet::XLSX - Perl extension for reading MS Excel 2007 files;

SYNOPSIS

	use Text::Iconv;
	my $converter = Text::Iconv -> new ("utf-8", "windows-1251");

	# Text::Iconv is not really required.
	# This can be any object with the convert method. Or nothing.

	use Spreadsheet::XLSX;

	my $excel = Spreadsheet::XLSX -> new ('test.xlsx', $converter);

	foreach my $sheet (@{$excel -> {Worksheet}}) {

	       printf("Sheet: %s
", $sheet->{Name});

	       $sheet -> {MaxRow} ||= $sheet -> {MinRow};

		foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {

		       $sheet -> {MaxCol} ||= $sheet -> {MinCol};

		       foreach my $col ($sheet -> {MinCol} ..  $sheet -> {MaxCol}) {

			       my $cell = $sheet -> {Cells} [$row] [$col];

			       if ($cell) {
				   printf("( %s , %s ) => %s
", $row, $col, $cell -> {Val});
			       }

		       }

	       }

	}

DESCRIPTION

       This module is a (quick and dirty) emulation of Spreadsheet::ParseExcel for Excel 2007 (.xlsx) file format.  It supports styles and many of
       Excel's quirks, but not all.  It populates the classes from Spreadsheet::ParseExcel for interoperability; including Workbook, Worksheet,
       and Cell.

SEE ALSO

       Text::CSV_XS, Text::CSV_PP
	 http://search.cpan.org/~hmbrand/

	 A pure perl version is available on http://search.cpan.org/~makamaka/

       Spreadsheet::ParseExcel
	 http://search.cpan.org/~kwitknr/

       Spreadsheet::ReadSXC
	 http://search.cpan.org/~terhechte/

       Spreadsheet::BasicRead
	 http://search.cpan.org/~gng/ for xlscat likewise functionality (Excel only)

       Spreadsheet::ConvertAA
	 http://search.cpan.org/~nkh/ for an alternative set of cell2cr () / cr2cell () pair

       Spreadsheet::Perl
	 http://search.cpan.org/~nkh/ offers a Pure Perl implementation of a spreadsheet engine. Users that want this format to be supported in
	 Spreadsheet::Read are hereby motivated to offer patches. It's not high on my todo-list.

       xls2csv
	 http://search.cpan.org/~ken/ offers an alternative for my "xlscat -c", in the xls2csv tool, but this tool focusses on character encoding
	 transparency, and requires some other modules.

       Spreadsheet::Read
	 http://search.cpan.org/~hmbrand/ read the data from a spreadsheet (interface module)

AUTHOR

       Dmitry Ovsyanko, <do@eludia.ru<gt>, http://eludia.ru/wiki/

       Patches by:

	       Steve Simms
	       Joerg Meltzer
	       Loreyna Yeung
	       Rob Polocz
	       Gregor Herrmann
	       H.Merijn Brand
	       endacoe
	       Pat Mariani
	       Sergey Pushkin

ACKNOWLEDGEMENTS

	       Thanks to TrackVia Inc. (http://www.trackvia.com) for paying for Rob Polocz working time.

COPYRIGHT AND LICENSE

       Copyright (C) 2008 by Dmitry Ovsyanko

       This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.8 or,
       at your option, any later version of Perl 5 you may have available.

perl v5.10.1							    2010-05-16						    Spreadsheet::XLSX(3pm)