[Solved] Create/export Columns to text file

[isotope23]

Hi,

I'm looking for the following in calc -

create a directory name from the sheet name, create a text file then export a column (say A1-A50) into the text file.

I did something like this a few years ago in VB for excel. Looking for similar functionality in Calc.

Thanks

[FJCC]

A very simple version is

Code: Select all

oSheets = ThisComponent.Sheets
oSheet = oSheets.getByIndex(0) 'First Sheet
oCellrange = oSheet.getCellrangeByName("A1:A50")
ValueMatrix = oCellrange.DataArray 
DirName = "c:\users\fjcc\desktop\" & oSheet.Name
MkDir(DirName)
FileChan = FreeFile
Open  DirName & "\NewFile" for Output as FileChan
for i = 0 to 49
	Write #FileChan, ValueMatrix(i)(0)
next i
Close #FileChan

[isotope23]

Thanks,

That's pretty close, it's throwing quotes around each line in the text file though -

[isotope23]

I figured the quotes out, using Print instead of write...

Last question, is there a way to kick the sub off by checking if a single cell has been modified?

Example, say cell B1 has "n" and changed to "y" I'd want the sub to run.

Searching the forums, I think I may have found the answer - a listener routine.....

Will give it a try and post feedback here

Thanks!

[isotope23]

So I got a listener to work, e.g. a change in anything from cells 1-10 runs the macro again.
However even when I call the listener from Main I still need to manually run the macro at least once to get it to work.

Can I have the macro start as soon as the file is open? (I'm trying to make this as simple and as automated as possible for end user)

[isotope23]

Okay I believe I figured out everything I need - went into tools/customize and assigned my main sub to the "open document" action.
It looks like it executes the sub, registers the listener and writes the file if the listener detects a change....


FJCC, thanks for the help with the example snippet, it should get me where I need to go with the rest...