How to create text file from selected excel data

  • Thread starter Thread starter Per Magnus L?vold
  • Start date Start date
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
 
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
 
Back
Top