• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 142
  • Last Modified:

How to read from excel and export to text file?

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
kunghui80
Asked:
kunghui80
  • 3
  • 2
1 Solution
 
KimputerCommented:
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
 
kunghui80Author Commented:
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
 
KimputerCommented:
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
 
kunghui80Author Commented:
Thanks Kimputer, the script works great as desired.  Thanks for fast response and effort!
0
 
kunghui80Author Commented:
The script works as desired.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now