Export Excel Range to CSV or Tab Delimited file

[ca143508]

Hey Guys,

Looking for any help you can give me on this one. I need to get a script together that will do the following:

a) Prompt to browse for the default spreadsheet

Prompt to determine how many rows should be read

c) Read each row of data starting in cell A9 to M9 and down the number of rows in step (

d) Transfer each row of data to a text file with each cells data separated by commas.

The prompts and stuff are easy obviously it's reading the data from Excel and transfering it to the text file I can figure out. Any help would be greatly appreciated.

Thanks,

Michael

Edited January 9, 2007 by ca143508

[PerryRaptor]

May be this might help you...Also, I do not know who originally wrote this code.

You will probably be interested in this part of the code...

$Values = ""

For $c In $NewBook.Sheets("Sheet1").Range("a1:n9").Cells

$Values = $Values & $c.Text & @TAB

Next

msgbox(0,"Values Read",StringStripWS($Values,3))

$Excel = ObjCreate("Excel.Application"); Create an Excel Object
if @error then 
  Msgbox (0,"","Error creating Excel object. Error code: " & @error)
  exit
endif
if not IsObj($Excel) then 
  Msgbox (0,"ExcelTest","I'm sorry, but creation of an Excel object failed.")
  exit
endif
$Excel.Visible = 1  ; Let the guy show himself - Set to 0 to run excel invisible
$NewBook = $Excel.workbooks.add  ; Add a new workbook
; Example: Fast Fill some cells with there address
Msgbox (0,"","Click 'ok' to fastfill some cells")
For $c In $NewBook.Sheets("Sheet1").Range("a1:n1").Cells
    $c.Value = $c.Address
    For $i = 1 To 8
        $c.Offset($i,0).Value = $c.Offset($i,0).Address
    Next
Next

msgbox(0,"ExcelTest","Click 'ok' to read the cells in each column.")
$Values = ""
For $c In $NewBook.Sheets("Sheet1").Range("a1:n9").Cells
    $Values = $Values & $c.Text & @TAB
Next
msgbox(0,"Values Read",StringStripWS($Values,3))
$Excel.activeworkbook.saved = 1; To prevent 'yes/no' questions from Excel
$Excel.quit       ; Get rid of him.
$Excel = 0        ; Loose this object.
              ; Object will also be automatically discarded when you exit the script
exit

[ca143508]

May be this might help you...Also, I do not know who originally wrote this code.

You will probably be interested in this part of the code...

$Values = ""

For $c In $NewBook.Sheets("Sheet1").Range("a1:n9").Cells

$Values = $Values & $c.Text & @TAB

Next

msgbox(0,"Values Read",StringStripWS($Values,3))

$Excel = ObjCreate("Excel.Application"); Create an Excel Object
if @error then 
  Msgbox (0,"","Error creating Excel object. Error code: " & @error)
  exit
endif
if not IsObj($Excel) then 
  Msgbox (0,"ExcelTest","I'm sorry, but creation of an Excel object failed.")
  exit
endif
$Excel.Visible = 1; Let the guy show himself - Set to 0 to run excel invisible
$NewBook = $Excel.workbooks.add; Add a new workbook
; Example: Fast Fill some cells with there address
Msgbox (0,"","Click 'ok' to fastfill some cells")
For $c In $NewBook.Sheets("Sheet1").Range("a1:n1").Cells
    $c.Value = $c.Address
    For $i = 1 To 8
        $c.Offset($i,0).Value = $c.Offset($i,0).Address
    Next
Next

msgbox(0,"ExcelTest","Click 'ok' to read the cells in each column.")
$Values = ""
For $c In $NewBook.Sheets("Sheet1").Range("a1:n9").Cells
    $Values = $Values & $c.Text & @TAB
Next
msgbox(0,"Values Read",StringStripWS($Values,3))
$Excel.activeworkbook.saved = 1; To prevent 'yes/no' questions from Excel
$Excel.quit     ; Get rid of him.
$Excel = 0      ; Loose this object.
            ; Object will also be automatically discarded when you exit the script
exit

Thanks for thi code PR. I have made some changes to get it pretty close to the mark but am struggling with one last item. When it hits the end of the row, I need the file write line to get an @CRLF command. Any ideas??? I have added the | so that should we need to put the data back into a dbase or excel it can be identified as delimited.

$file = FileOpenDialog("","C:\","Microsoft Excel (*.xls)",1)

$exceldoc = ObjGet($file)

$Values = ""

For $c In $exceldoc.Sheets("Sheet1").Range("a8:t150").Cells

$Values = $Values & $c.Text & "|"

Next

FileWriteLine("C:\Test.txt",StringStripWS($Values,7) & @CRLF)

exit

Cheers,

Michael

Edited January 9, 2007 by ca143508

[PerryRaptor]

Well it's hard to say without seeing all of th code...So I'm assuming that the range of cells you where interested in had been sent to the file Test.txt.

You can try something like this...

$file = FileOpen("c:\test.txt", 2)

....................................

...all of your code here...

....................................

FileWriteLine($file,StringStripWS($Values,7) & @CRLF)

Exit

FileWriteLine($file," " & @CRLF)

FileClose($file)

[ca143508]

Well it's hard to say without seeing all of th code...So I'm assuming that the range of cells you where interested in had been sent to the file Test.txt.

You can try something like this...

$file = FileOpen("c:\test.txt", 2)

....................................

...all of your code here...

....................................

FileWriteLine($file,StringStripWS($Values,7) & @CRLF)

Exit

FileWriteLine($file," " & @CRLF)

FileClose($file)

What I have added to my previous post is all the code I was using to dump the contents into a text file. The problem is it is writing it in all one long line. I want each row of the original spreadsheet to go on it's own line in the text file. So ie/ a9:t9 on the first line. a10:t10 on the second and so on. Does that make sense???

Cheers,

Michael

Edited January 10, 2007 by ca143508

[PerryRaptor]

This should do the trick...

$Excel = ObjCreate("Excel.Application"); Create an Excel Object
if @error then 
  Msgbox (0,"","Error creating Excel object. Error code: " & @error)
  exit
endif
if not IsObj($Excel) then 
  Msgbox (0,"ExcelTest","I'm sorry, but creation of an Excel object failed.")
  exit
endif
$Excel.Visible = 1; Let the guy show himself - Set to 0 to run excel invisible
$NewBook = $Excel.workbooks.add; Add a new workbook
; Example: Fast Fill some cells with there address
Msgbox (0,"","Click 'ok' to fastfill some cells")
For $c In $NewBook.Sheets("Sheet1").Range("a1:n1").Cells
    $c.Value = $c.Address
    For $i = 1 To 8
        $c.Offset($i,0).Value = $c.Offset($i,0).Address
    Next
Next
$TextFile = "C:\TextFile.txt"
FileOpen($TextFile, 2)
msgbox(0,"Excel2Text","Click 'ok' to copy cells into a single column text file")
$Values = ""
For $c In $NewBook.Sheets("Sheet1").Range("a1:n9").Cells
    $Values = $c.Text & @TAB
    FileWriteLine($TextFile, $Values)
Next
$Excel.activeworkbook.saved = 1; To prevent 'yes/no' questions from Excel
$Excel.quit    ; Get rid of him.
$Excel = 0     ; Loose this object.
           ; Object will also be automatically discarded when you exit the script

FileClose($TextFile)
Run("NotePad.exe " & $TextFile,"", @SW_MAXIMIZE)
exit

[ca143508]

Thanks for this. I'll give it a try and see how it goes.

Michael.