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
.