Open new file for output from Calc using macro

Creating a macro - Writing a Script - Using the API

Open new file for output from Calc using macro

Postby tomsullivan » Mon Sep 19, 2011 10:40 pm

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   Expand viewCollapse view
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
tomsullivan
 
Posts: 4
Joined: Wed Jan 26, 2011 10:23 pm

Re: open new file for output from calc using macro

Postby squenson » Mon Sep 19, 2011 11:15 pm

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   Expand viewCollapse view
Filename = "c:\\data.txt" ' Define file name
LibreOffice 4.2.3.3. on Ubuntu 14.04
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

Postby Charlie Young » Mon Sep 19, 2011 11:41 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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
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

Postby tomsullivan » Tue Sep 20, 2011 3:20 pm

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
tomsullivan
 
Posts: 4
Joined: Wed Jan 26, 2011 10:23 pm

Re: Open new file for output from Calc using macro

Postby Charlie Young » Tue Sep 20, 2011 6:55 pm

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   Expand viewCollapse view
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
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

Postby tomsullivan » Wed Sep 21, 2011 3:21 pm

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
tomsullivan
 
Posts: 4
Joined: Wed Jan 26, 2011 10:23 pm

Re: Open new file for output from Calc using macro

Postby Villeroy » Wed Sep 21, 2011 6:17 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27094
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Open new file for output from Calc using macro

Postby Charlie Young » Wed Sep 21, 2011 6:40 pm

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
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 8 guests