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::utility2007

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

NAME

       Spreadsheet::XLSX::Utility2007 - Utility function for Spreadsheet::XLSX

SYNOPSIS

	   use strict;
	   #Declare
	   use Spreadsheet::XLSX::Utility qw(ExcelFmt ExcelLocaltime LocaltimeExcel);

	   #Convert localtime ->Excel Time
	   my $iBirth = LocaltimeExcel(11, 10, 12, 23, 2, 64);
				      # = 1964-3-23 12:10:11
	   print $iBirth, "
";       # 23459.5070717593

	   #Convert Excel Time -> localtime
	   my @aBirth = ExcelLocaltime($iBirth, undef);
	   print join(":", @aBirth), "
";   # 11:10:12:23:2:64:1:0

	   #Formatting
	   print ExcelFmt('yyyy-mm-dd', $iBirth), "
"; #1964-3-23
	   print ExcelFmt('m-d-yy', $iBirth), "
";	# 3-23-64
	   print ExcelFmt('#,##0', $iBirth), "
";	# 23,460
	   print ExcelFmt('#,##0.00', $iBirth), "
";	# 23,459.51
	   print ExcelFmt('"My Birthday is (m/d):" m/d', $iBirth), "
";
					     # My Birthday is (m/d): 3/23

DESCRIPTION

       Spreadsheet::XLSX::Utility exports utility functions concerned with Excel format setting.

       ExcelFmt is used by Spreadsheet::XLSX::Fmt2007.pm which is used by Spreadsheet::XLSX.

Functions
       This module can export 3 functions: ExcelFmt, ExcelLocaltime and LocaltimeExcel.

   ExcelFmt
       $sTxt = ExcelFmt($sFmt, $iData [, $i1904]);

       $sFmt is a format string for Excel. $iData is the target value.	If $flg1904 is true, this functions assumes that epoch is 1904.  $sTxt is
       the result.

       For more detail and examples, please refer sample/chkFmt.pl in this distribution.

       ex.

   ExcelLocaltime
       ($iSec, $iMin, $iHour, $iDay, $iMon, $iYear, $iwDay, $iMSec) =
		   ExcelLocaltime($iExTime [, $flg1904]);

       ExcelLocaltime converts time information in Excel format into Perl localtime format.  $iExTime is a time of Excel. If $flg1904 is true,
       this functions assumes that epoch is 1904.  $iSec, $iMin, $iHour, $iDay, $iMon, $iYear, $iwDay are same as localtime.  $iMSec means
       1/1,000,000 seconds(ms).

   LocaltimeExcel
       $iExTime = LocaltimeExcel($iSec, $iMin, $iHour, $iDay, $iMon, $iYear [,$iMSec] [,$flg1904])

       LocaltimeExcel converts time information in Perl localtime format into Excel format .  $iSec, $iMin, $iHour, $iDay, $iMon, $iYear are same
       as localtime.

       If $flg1904 is true, this functions assumes that epoch is 1904.	$iExTime is a time of Excel.

   col2int
       $iInt = col2int($sCol);

       converts a excel row letter into an int for use in an array

       This function was contributed by Kevin Mulholland.

   int2col
       $sCol = int2col($iRow);

       convert a column number into column letters NOET: This is quite a brute force coarse method does not manage values over 701 (ZZ)

       This function was contributed by Kevin Mulholland.

   sheetRef
       ($iRow, $iCol) = sheetRef($sStr);

       convert an excel letter-number address into a useful array address NOTE: That also Excel uses X-Y notation, we normally use Y-X in arrays
       $sStr, excel coord (eg. A2).

       This function was contributed by Kevin Mulholland.

   xls2csv
       $sCsvTxt = xls2csv($sFileName, $sRegion, $iRotate);

       convert a chunk of an excel file into csv text chunk $sRegions = "sheet-colrow:colrow" (ex. '1-A1:B2' means 'A1:B2' for sheet 1) $iRotate
       = 0 or 1 (output should be rotated or not)

       This function was contributed by Kevin Mulholland.

AUTHOR

       Rob Polocz rob.polocz@trackvia.com based on work by for Spreadsheet::ParseExcel by Kawai Takanori (Hippo2000) used with permission

SEE ALSO

       Spreadsheet::ParseExcel, Spreadsheet::WriteExcel

COPYRIGHT

       This module is part of the Spreadsheet::XLSX distribution.

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