How to create text file from selected excel data

P

Per Magnus L?vold

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
 
S

Sandy V

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
 
Top