Solved

How to read from excel and export to text file?

Posted on 2015-02-02
5
118 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

726 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