Solved

How to read from excel and export to text file?

Posted on 2015-02-02
5
111 Views
Last Modified: 2015-02-03
Dear Experts,

I have an excel spreadsheet (SampleWorksheet.xlsx as attached) stored at remote network path i.e. \\myserver\dept\share\SampleWorksheet.xlsx. For this, I need to use VBScript to achieve two purpose. It can be in a single VB script or two seperate VBScript to achieve this.

Column A: Servers
Column B: Project
Column C: Status
Column D: Environment

1) Read the SampleWorksheet.xlsx and export list of servers name with status Live to a text file named AllLiveServers.txt.

2) Read the SampleWorksheet.xlsx and export list of servers name with status Live and environment Prod only to a text file named ProdLiveServers.txt.

Would appreaciate if any Experts could assist to achieve this method.

Thanks.

Regards,
Kung Hui
SampleWorksheet.xlsx
0
Comment
Question by:kunghui80
  • 3
  • 2
5 Comments
 
LVL 35

Expert Comment

by:Kimputer
ID: 40585787
Here it is:

outputfile1 = "F:\Downloads\AllLiveServers.txt"
outputfile2 = "F:\Downloads\ProdLiveServers.txt"

Set fso = wscript.CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")


objExcel.Visible = True
objExcel.Workbooks.Open("\\test-server\e$\SampleWorksheet.xlsx")

Set logFile1 = fso.CreateTextFile(outputfile1, True)
Set logFile2 = fso.CreateTextFile(outputfile2, True)

i = 1
Do until objExcel.Cells(i,3).Value = ""
	if objExcel.Cells(i,3).Value = "Live" then
		logFile1.WriteLine(objExcel.Cells(i,1).Value)
	end if
	i = i + 1
Loop


i = 1
Do until objExcel.Cells(i,4).Value = ""
	if objExcel.Cells(i,4).Value = "PROD" then
		logFile2.WriteLine(objExcel.Cells(i,1).Value)
	end if
	i = i + 1
Loop


logFile1.Close
Set logFile1 = Nothing
logFile2.Close
Set logFile2 = Nothing
objExcel.Quit
Set objExcel = Nothing

Open in new window


Obviously, edit 3 lines (2 x txt file location, 1 x excel file loc)
0
 
LVL 2

Author Comment

by:kunghui80
ID: 40585802
Hi Kimputer,
General it works good if all data are populated.
I notice one bug that if in between any of the entry with blank Status/Environment, the script will stop there and doesn't capture whatever server name next to that.
0
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 40585819
Amended (still requires the first column NOT to have blank spaces). It's because your sample didn't show blank spaces, making me think it won't happen. In the future, when posting samples, try to put all possible situations you can think of in the sample.

outputfile1 = "F:\Downloads\AllLiveServers.txt"
outputfile2 = "F:\Downloads\ProdLiveServers.txt"

Set fso = wscript.CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")


objExcel.Visible = True
objExcel.Workbooks.Open("\\test-server\e$\SampleWorksheet.xlsx")

Set logFile1 = fso.CreateTextFile(outputfile1, True)
Set logFile2 = fso.CreateTextFile(outputfile2, True)

i = 1
Do until objExcel.Cells(i,1).Value = ""
	if objExcel.Cells(i,3).Value = "Live" then
		logFile1.WriteLine(objExcel.Cells(i,1).Value & "," & objExcel.Cells(i,2).Value & "," & objExcel.Cells(i,3).Value & ","  & objExcel.Cells(i,4).Value)
	end if
	i = i + 1
Loop


i = 1
Do until objExcel.Cells(i,1).Value = ""
	if objExcel.Cells(i,4).Value = "PROD" then
		logFile2.WriteLine(objExcel.Cells(i,1).Value & "," & objExcel.Cells(i,2).Value & "," & objExcel.Cells(i,3).Value & ","  & objExcel.Cells(i,4).Value)
	end if
	i = i + 1
Loop


logFile1.Close
Set logFile1 = Nothing
logFile2.Close
Set logFile2 = Nothing
objExcel.Quit
Set objExcel = Nothing

Open in new window

0
 
LVL 2

Author Comment

by:kunghui80
ID: 40585886
Thanks Kimputer, the script works great as desired.  Thanks for fast response and effort!
0
 
LVL 2

Author Closing Comment

by:kunghui80
ID: 40585887
The script works as desired.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
Hello again, all.  For those of you that have been following along, you'll know that this is my third article on this topic (though it is not Part III).  This article is sort of remedial, and probably the topic with which I should have started the s…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question