How to create text file from selected excel data
 

Hi,
I'm trying to figure out how to create a plain text file based partly
on data from a excel sheet.

For this I beleive I must use a scripting language which can access
Excel data and create a txt file.
I'm working in a windows XP environment. So far, I've been wondering
if vbs (VB scipt) is the solution. I'm not very familiar with VB,
however, and am not sure how I should access Excel data.

I'll try to explain what I wish to do with a sort of pseudo-code:

BEGIN
myExcelSource = new ExcelReader(c:\excelData.xls)
destination = new textFile(c:\result.txt)
FOR (amount_lines_in_excel_sheet) {
destination.writeLine("INSERT INTO test_table (excel_data1,
....excel_dataX) VALUES (" || myExcelSource.col(X).row(Y) || ... ");")
END FOR
END

Could anybody tell me if vbs is capable of doing this, or if I perhaps
should look at another scripting language that is capable of this (or
similar)?

Regards, Per Magnus

How to create text file from selected excel data
 

vbs should be able to do it. You want to use Automation.

http://support.microsoft.com/?id=219151
Q219151 - HOWTO: Automate Excel 97 and Excel 2000 from Visual Basic

approach in the article would not be different for vbs.



http://support.microsoft.com/?id=198703
HOWTO: Automating Excel From Client-Side VBScript


--
Regards,
Tom Ogilvy

"Per Magnus L?vold" wrote in message
m...
Hi,
I'm trying to figure out how to create a plain text file based partly
on data from a excel sheet.

For this I beleive I must use a scripting language which can access
Excel data and create a txt file.
I'm working in a windows XP environment. So far, I've been wondering
if vbs (VB scipt) is the solution. I'm not very familiar with VB,
however, and am not sure how I should access Excel data.

I'll try to explain what I wish to do with a sort of pseudo-code:

BEGIN
myExcelSource = new ExcelReader(c:\excelData.xls)
destination = new textFile(c:\result.txt)
FOR (amount_lines_in_excel_sheet) {
destination.writeLine("INSERT INTO test_table (excel_data1,
...excel_dataX) VALUES (" || myExcelSource.col(X).row(Y) || ... ");")
END FOR
END

Could anybody tell me if vbs is capable of doing this, or if I perhaps
should look at another scripting language that is capable of this (or
similar)?

Regards, Per Magnus

How to create text file from selected excel data
 

Per Magnus,

Not sure if this vba macro might be of any use to you:

Sub Range2Txt()
Dim MyData As DataObject

Set MyData = New DataObject

ActiveSheet.UsedRange.Copy
'Selection.Copy 'or whatever range
MyData.GetFromClipboard

Open "C:\Temp\Range2Txt_Test1.txt" For Output As #1
Print #1, MyData.GetText(1)
Close #1

Application.CutCopyMode = False
End Sub

I posted this a while back, Dave Peterson followed up with
these comments:


if you get an error on the DataObject line, you can set a
reference:
Inside the VBE, tools|references|
put a check mark in front of:
Microsoft Forms 2.0 Object Library

(or just insert|userform will create the reference, too.)

But I did have a couple of problems. If I had a pretty
large usedrange, sometimes I'd get an error on the
..getfromclipboard line if I just ran the code.

But if I stepped through it with F8's, it never failed.

(Maybe a combination of lack of resources/windows
version???)

And if you want a little more control on what to save, you
may want to look at Chip Pearson's site. He has example
code that exports a range to a text file.
(It's more complex, but much more customizable.)

http://www.cpearson.com/excel/imptext.htm


Regards,
Sandy


-----Original Message-----
Hi,
I'm trying to figure out how to create a plain text file
based partly
on data from a excel sheet.

For this I beleive I must use a scripting language which
can access
Excel data and create a txt file.
I'm working in a windows XP environment. So far, I've
been wondering
if vbs (VB scipt) is the solution. I'm not very familiar
with VB,
however, and am not sure how I should access Excel data.

I'll try to explain what I wish to do with a sort of
pseudo-code:

BEGIN
myExcelSource = new ExcelReader(c:\excelData.xls)
destination = new textFile(c:\result.txt)
FOR (amount_lines_in_excel_sheet) {
destination.writeLine("INSERT INTO test_table
(excel_data1,
....excel_dataX) VALUES (" || myExcelSource.col(X).row(Y)
|| ... ");")
END FOR
END

Could anybody tell me if vbs is capable of doing this, or
if I perhaps
should look at another scripting language that is capable
of this (or
similar)?

Regards, Per Magnus
.