?
Solved

How to read from excel and export to text file?

Posted on 2015-02-02
5
Medium Priority
?
138 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 37

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 37

Accepted Solution

by:
Kimputer earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month15 days, 10 hours left to enroll

850 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