[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

Excel Cell to TXT Document via Batch Script???

Do you know of a way to pull the text from a cell from an excel spreadsheet into a text document? Maybe a batch script, which I can then set to autorun via task scheduler???
0
pghzooit
Asked:
pghzooit
  • 9
  • 8
  • 5
  • +2
3 Solutions
 
Calvin BrineCommented:
Are you working with VBA from Excel, VB6 or VB.net?  I can give you code for each of them.  I would suggest setting up an ADO connection to the excel workbook, and querying the data from it.  You could also use the Excel object to do the same thing.  Let me know which approach you are interested in.

Cal
0
 
MSE-dwellsCommented:
You can't adequately read Excel files from pure batch, you'll need something else -- exporting it is, I guess, not an option since that seems to be your stated goal ... or perhaps you are able to export the spreadsheet as a CSV which is more manageable.  Either way, how doesw the script know when it's got what you need, is it purely the cell reference or something more?
0
 
pghzooitAuthor Commented:
I am starting from scratch so I am open to everything. Do you have a suggestion?
0
Technology Partners: 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!

 
kacorretiredCommented:
read:
Retrieving Excel 2007 Cell Values http://msdn2.microsoft.com/en-us/library/bb332058.aspx

Janos
0
 
Calvin BrineCommented:
My thoughts would be VB6 or VB.net since you want to schedule(You can do this with excel, but it's not easy).  By the sounds of it you are looking for some specific cells for the output to text, so I would go with a reference to the Excel object, since you can point to specific cells.  Then use the FSO textstream to dump the cell contents to the txt file.
Couple of questions.
1.  What is the sheetname and cell addresses that you need to dump to the txt file.
2.  What are the specifications for the text file output?

Cal
0
 
pghzooitAuthor Commented:
1.  What is the sheetname and cell addresses that you need to dump to the txt file.
2007.xls
Sheet Name: DEC 07
Cell Address: Z46
2.  What are the specifications for the text file output?
Just dump the number from a cell into a TXT file that will be overwritten everytime this runs


Hope that helps
0
 
pghzooitAuthor Commented:
Janos,

That is a very nice feature in Office 2007.  However, the user that makes the spreadsheet uses Office 2003.
0
 
Calvin BrineCommented:
Here's some quick and dirty VB6 code, with no error checking,  to get you what you want.  I've set it for late binding so you don't need to worry about the references.

Private Sub Form_Load()
Dim xlApp As Object
Dim wb As Object, ws As Object, cell As Object
Dim fso As Object, ts As Object

Set xlApp = New Excel.Application
Set wb = Workbooks.Open("c:\test\2007.xls")
Set ws = wb.Sheets("DEC 07")
Set cell = ws.Range("Z46")

Set fso = New FileSystemObject
Set ts = fso.CreateTextFile("C:\test\output.txt")

ts.Write cell
ts.Close

xlApp.Quit
Unload Me
End Sub

HTH
Cal
0
 
Calvin BrineCommented:
Sorry, I tried it without the references and ran into some problems.  You will need to reference the microsoft scripting runtime for the filesystemobject, but not for the Excel object.

Private Sub Form_Load()
Dim xlApp As Object
Dim wb As Object, ws As Object, cell As Object
Dim fso As FileSystemObject, ts As TextStream

Set xlApp = CreateObject("Excel.application")
Set wb = xlApp.Workbooks.Open("c:\test\2007.xls")
Set ws = wb.Sheets("DEC 07")
Set cell = ws.Range("Z46")

Set fso = New FileSystemObject
Set ts = fso.CreateTextFile("C:\test\output.txt")

ts.Write cell
ts.Close

xlApp.Quit
Unload Me
End Sub
0
 
RobSampsonCommented:
Hi, here's a VBS file that can be run from Windows that will do the same thing as Cbrine's solution.

'==============
Set objExcel = CreateObject("Excel.application")
Set objWB = objExcel.Workbooks.Open("c:\test\2007.xls")
objExcel.Visible = False
objWB.Sheets("DEC 07").Activate
strCellValue = objWB.Sheets("DEC 07").Range("Z46").Value

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.CreateTextFile("C:\test\output.txt", True)

objOutputFile.Write strCellValue
objOutputFile.Close

objExcel.Quit
'==============

Regards,

Rob.
0
 
pghzooitAuthor Commented:
cbrine,
excuse my ignorance but what extension do I save the file as? I am very new to scripting.


Rob,
Your VBS file works except the excel document asks if you want to save the changes after running the script. Is there a way in the script to not save the changes?



Thanks for the help
0
 
RobSampsonCommented:
Yes, you can make it not save changes, using an extra parameter of True on the Open method, to make it open in Read Only, and then prevent alerts from displaying on Close:

'==============
Set objExcel = CreateObject("Excel.application")
' The first param is FileName, second is UpdateLinks, third is ReadOnly
Set objWB = objExcel.Workbooks.Open("c:\test\2007.xls", False, True)
objExcel.Visible = False
objWB.Sheets("DEC 07").Activate
strCellValue = objWB.Sheets("DEC 07").Range("Z46").Value

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.CreateTextFile("C:\test\output.txt", True)

objOutputFile.Write strCellValue
objExcel.DisplayAlerts = False
objOutputFile.Close False
objExcel.DisplayAlerts = True

objExcel.Quit
'==============

Regards,

Rob.
0
 
pghzooitAuthor Commented:
Rob,

I receive an error after running the new script.

Follow this link to see the error:
http://www.pittsburghzoo.org/upload/Image/VBS%20error.jpg
0
 
RobSampsonCommented:
Oh, right, yeah, I put on the close of the text file, not the Excel file....try this:

'==============
Set objExcel = CreateObject("Excel.application")
' The first param is FileName, second is UpdateLinks, third is ReadOnly
Set objWB = objExcel.Workbooks.Open("c:\test\2007.xls", False, True)
objExcel.Visible = False
objWB.Sheets("DEC 07").Activate
strCellValue = objWB.Sheets("DEC 07").Range("Z46").Value

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.CreateTextFile("C:\test\output.txt", True)

objOutputFile.Write strCellValue
objOutputFile.Close False

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Close False
objExcel.DisplayAlerts = True

objExcel.Quit
'==============

Regards,

Rob.
0
 
RobSampsonCommented:
Doh!  And take the False off from these two lines:
objOutputFile.Write strCellValue
objOutputFile.Close False

so it is just
objOutputFile.Write strCellValue
objOutputFile.Close

Regards,

Rob.
0
 
pghzooitAuthor Commented:
Rob,

That works great!!!
Is there a way to add text to the text document it outputs? If so, can it also be centered?

For example:

This is the attendence for the year 2007:

{Excel Cell Z46}
0
 
Calvin BrineCommented:
pghzooit,
  There is no way to center or left or right a text document.  Whatever you output is what is in the text file.  About the only thing supported are <CR> and <TB> and a few other print functions.  You want formating you need to use another format.

HTH
Cal
0
 
RobSampsonCommented:
Hi, as Cbrine has stated, you cannot "centre" text inside a plain text document, but you can "pad" the front of the string, with something like:

'================
' Pad the strCellValue to a total of 20 characters, with spaces to it's left
strCellValue Pad_String(strCellValue, 20, "left", " ")

Function Pad_String(strOriginalString, intTotalLengthRequired, strPaddingSide, strCharacterToPadWith)
      If LCase(strPaddingSide) <> "left" And LCase(strPaddingSide) <> "right" Then
            strPaddingSide = "right"
      End If
      Select Case LCase(strPaddingSide)
            Case "left"
                  Pad_String = Right(String(intTotalLengthRequired, Left(strCharacterToPadWith, 1)) & strOriginalString, intTotalLengthRequired)
            Case "right"
                  Pad_String = Left(strOriginalString & String(intTotalLengthRequired, Left(strCharacterToPadWith, 1)), intTotalLengthRequired)
      End Select
End Function
'================


Regards,

Rob.
0
 
pghzooitAuthor Commented:
Rob,

Where in the script should the "padding" function go?
I recieve an error after adding it.

http://www.pittsburghzoo.org/upload/Image/VBSerror.jpg
0
 
RobSampsonCommented:
Oh, whoops, you need an equals sign in
strCellValue Pad_String(strCellValue, 20, "left", " ")

to it becomes:
strCellValue = Pad_String(strCellValue, 20, "left", " ")

and the above line goes anywhere you need it to be used, and the function code itself, I usually put the whole block right at the bottom, outside of the main code.

Regards,

Rob.
0
 
pghzooitAuthor Commented:
Here is what I currently have. It still is not "padding."  Am I missing something?




Set objExcel = CreateObject("Excel.application")
' The first param is FileName, second is UpdateLinks, third is ReadOnly
Set objWB = objExcel.Workbooks.Open("C:\Documents and Settings\Administrator.PGHZOO\Desktop\2007.xls", False, True)
objExcel.Visible = False
objWB.Sheets("DEC 07").Activate
strCellValue = objWB.Sheets("DEC 07").Range("Z46").Value

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.CreateTextFile("C:\Documents and Settings\Administrator.PGHZOO\Desktop\output.txt", True)

objOutputFile.Write strCellValue
objOutputFile.Close

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Close False
objExcel.DisplayAlerts = True

objExcel.Quit


' Pad the strCellValue to a total of 20 characters, with spaces to it's left
strCellValue = Pad_String(strCellValue, 20, "left", " ")

Function Pad_String(strOriginalString, intTotalLengthRequired, strPaddingSide, strCharacterToPadWith)
      If LCase(strPaddingSide) <> "left" And LCase(strPaddingSide) <> "right" Then
            strPaddingSide = "right"
      End If
      Select Case LCase(strPaddingSide)
            Case "left"
                  Pad_String = Right(String(intTotalLengthRequired, Left(strCharacterToPadWith, 1)) & strOriginalString, intTotalLengthRequired)
            Case "right"
                  Pad_String = Left(strOriginalString & String(intTotalLengthRequired, Left(strCharacterToPadWith, 1)), intTotalLengthRequired)
      End Select
End Function
0
 
RobSampsonCommented:
Hi, all you were missing was that you used the Pad_String in the wrong spot....which was after you wrote it to the file.

So, instead, move this line
strCellValue = Pad_String(strCellValue, 20, "left", " ")

up, to just under this line:
strCellValue = objWB.Sheets("DEC 07").Range("Z46").Value

Which means you get the value from the sheet, then you pad it, then you write it to the file.

Regards,

Rob.
0
 
pghzooitAuthor Commented:
Sweet!!!! It works.

Thanks!!!!!
0
 
RobSampsonCommented:
No problem.  Sorry about the confusion with getting it to work...

Regards,

Rob.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 8
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now