Solved

How to read from excel and export to text file?

Posted on 2015-02-02
5
113 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/A_266-VBScri…
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 Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

730 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