Open new file for output from Calc using macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
tomsullivan
Posts: 4
Joined: Wed Jan 26, 2011 10:23 pm

Open new file for output from Calc using macro

Post by tomsullivan »

I have a workbook "newcioinvoice.ods" that contains 4 worsheets, The third worksheet "DATA" contains 10 rows of data each with 11 columns - all cells contain formulae and are populated based on the criteria from the 1st worksheet in the workbook "invoice". There may be from 1 to 10 rows of values versus formulae.

I want to append only the value rows to another workbook & Worksheet.

My major problem is I have been trying to write the macro to open a new workbook and after devera;l hours of searching the internet, I can't get a new workbook to open.
Here's the most recent macro I've tried . When I go to TOOLS and RUN the macro I get no errors but I also don't get a new file????????

Code: Select all

Sub dtatfile()
Dim FileNo As Integer
Dim CurrentLine As String
Dim Filename As String
 
Filename = "c:\data.txt"            ' Define file name 
FileNo = FreeFile               ' Establish free file handle
 
Open Filename For Output As #FileNo         ' Open file (writing mode)
Print #FileNo, "This is a line of text"      ' Save line 
Print #FileNo, "This is another line of text"   ' Save line 
Close #FileNo                  ' Close file
End Sub
Obviuosly, I'm missing something

Any suggestions would be appreciated
Open Office 3.2 on Windows 7 with Windows Live Mail
User avatar
squenson
Volunteer
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: open new file for output from calc using macro

Post by squenson »

Not sure of my answer as I can't test it (I don't have Windows), but try to double the "\" in the file name, something like:

Code: Select all

Filename = "c:\\data.txt" ' Define file name
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: open new file for output from calc using macro

Post by Charlie Young »

squenson wrote:Not sure of my answer as I can't test it (I don't have Windows), but try to double the "\" in the file name, something like:

Code: Select all

Filename = "c:\\data.txt" ' Define file name
Doubling the backslashes is for c programs. Shouldn't be necessary or functional here.

Tom's macro creates a .txt file named data.txt in the root directory on C:, and writes into it (it's from an example in Help)

Code: Select all

This is a line of text
This is another line of text
But Tom says he wants a new Calc workbook, I think, and that is done differently. The following creates a new workbook, then saves it to "NyNewWorkbook.ods" in the root directory.

After the workbook is created, it is manipulated using the NewWorkbook variable much as the active workbook is manipulated using ThisComponent.

Code: Select all

Sub LoadNewWorkbook
	Dim Doc As Object
	Dim NewWorkbook As Object
	Dim oDispatch As Object
	Dim Url As String
	Dim Args(1) As new com.sun.star.beans.PropertyValue
	Dim s As String
	
	Doc = ThisComponent
	
	Url = "file:///C:/MyNewWorkbook.ods"
	 	
 	Args(0).Name = "Hidden"
 	Args(0).Value = False
 	Args(1).Name = "MacroExecutionMode"
 	Args(1).Value = 4
 	 	
 	NewWorkbook = StarDesktop.loadComponentFromURL("private:factory/scalc", "_Blank", 0, Args)
 	
 	Args(0).Name = "FilterName"
 	Args(0).Value = "calc8"
 	Args(1).Name = "Overwrite"
 	Args(1).value = True
 	
 	NewWorkbook.storeAsURL(Url,Args)
 	
				
End Sub

Apache OpenOffice 4.1.1
Windows XP
tomsullivan
Posts: 4
Joined: Wed Jan 26, 2011 10:23 pm

Re: Open new file for output from Calc using macro

Post by tomsullivan »

Primary problem is the macro "appears" to run ( no error messages) but no new file is created. Tried the workbook create code above as well - still no new file.
Tom
Open Office 3.2 on Windows 7 with Windows Live Mail
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Open new file for output from Calc using macro

Post by Charlie Young »

tomsullivan wrote:Primary problem is the macro "appears" to run ( no error messages) but no new file is created. Tried the workbook create code above as well - still no new file.
Tom
Very odd, and hard to diagnose at a distance. The workbook create code should leave the MyNewWorkbook open as the active component.

This version gives before and after reports on whether MyNewWorkbook.ods exists:

Code: Select all

Sub LoadNewWorkbook
	Dim Doc As Object
	Dim NewWorkbook As Object
	Dim oDispatch As Object
	Dim Url As String
	Dim Args(1) As new com.sun.star.beans.PropertyValue
	Dim s As String
	
	Doc = ThisComponent
	
	Url = "file:///C:/MyNewWorkbook.ods"
	
	if FileExists(ConvertFromURL(URL)) then
		MsgBox(ConvertFromURL(URL) & " exists.")
	else
	 	MsgBox(ConvertFromURL(URL) & " does not exist.")
	endif 
 	Args(0).Name = "Hidden"
 	Args(0).Value = False
 	Args(1).Name = "MacroExecutionMode"
 	Args(1).Value = 4
 	 	
 	NewWorkbook = StarDesktop.loadComponentFromURL("private:factory/scalc", "_Blank", 0, Args)
 	
 	Args(0).Name = "FilterName"
 	Args(0).Value = "calc8"
 	Args(1).Name = "Overwrite"
 	Args(1).value = True
 	
 	NewWorkbook.storeAsURL(Url,Args)
 	if FileExists(ConvertFromURL(URL)) then
		MsgBox(ConvertFromURL(URL) & " exists.")
	else
	 	MsgBox(ConvertFromURL(URL) & " does not exist.")
	endif
				
End Sub

Apache OpenOffice 4.1.1
Windows XP
tomsullivan
Posts: 4
Joined: Wed Jan 26, 2011 10:23 pm

Re: Open new file for output from Calc using macro

Post by tomsullivan »

I think the problem was where I had the macro located. On my "organize macros" display I was showing MY MACROS, OPEN OFFICE MACROS and the spreadsheet name as selectable items and I had the macro under the spreadsheet. I copied the code you supplied into an existing macro under MY MACROS and low and behold it ran - the second set of code came up with a message box asking to create the new sheet and when I clicked OK, I got the new sheet. I replaced it with your initial code and it just created the new sheet - open on the desk top - without the message saying it didn't exist.

I am getting an error message on the macro on the NewWorkbook.storeAsURL(Url,Args) line saying BASIC RUNTIME ERROR AN EXCEPTION OCCURED TYPE com.sun.star.task ERROR CODE.IO EXCEPTION MESSAGE: .

This happens in both scripts

PS Thanks for all the help - if your feeling generous - what I'm trying to do is append data from a working sheet to a cumulative master file. The working sheet will then be cleared as the last step of the macro.
Open Office 3.2 on Windows 7 with Windows Live Mail
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Open new file for output from Calc using macro

Post by Villeroy »

Simply link your data.txt to a sheet in your spreadsheet document (Insert>SheetFromFile) and let your VB script exchange that file.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Open new file for output from Calc using macro

Post by Charlie Young »

tomsullivan wrote:I think the problem was where I had the macro located. On my "organize macros" display I was showing MY MACROS, OPEN OFFICE MACROS and the spreadsheet name as selectable items and I had the macro under the spreadsheet. I copied the code you supplied into an existing macro under MY MACROS and low and behold it ran - the second set of code came up with a message box asking to create the new sheet and when I clicked OK, I got the new sheet. I replaced it with your initial code and it just created the new sheet - open on the desk top - without the message saying it didn't exist.

I am getting an error message on the macro on the NewWorkbook.storeAsURL(Url,Args) line saying BASIC RUNTIME ERROR AN EXCEPTION OCCURED TYPE com.sun.star.task ERROR CODE.IO EXCEPTION MESSAGE: .

This happens in both scripts

PS Thanks for all the help - if your feeling generous - what I'm trying to do is append data from a working sheet to a cumulative master file. The working sheet will then be cleared as the last step of the macro.
You probably want the macro(s) in a module of the "working sheet" (whatever it's called), rather than anywhere in My Macros.

Our examples so far have created files in the root directory. I don't know about Windows 7, but there is often a limit on the number of files allowed there, and something like that may be the source of your errors -- especially if you are in the habit of putting files there willy-nilly, which is a bad idea.

For the rest, you can see if Villeroy's suggestion is suitable for your purpose. Soon after I joined the forums I helped someone with a similar project, and I have other things that may help. I would certainly prefer to offer guidance rather than doing all the work myself.
Apache OpenOffice 4.1.1
Windows XP
Post Reply