Page 1 of 1
Open new file for output from Calc using macro
Posted: Mon Sep 19, 2011 10:40 pm
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
Re: open new file for output from calc using macro
Posted: Mon Sep 19, 2011 11:15 pm
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
Re: open new file for output from calc using macro
Posted: Mon Sep 19, 2011 11:41 pm
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
Re: Open new file for output from Calc using macro
Posted: Tue Sep 20, 2011 3:20 pm
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
Re: Open new file for output from Calc using macro
Posted: Tue Sep 20, 2011 6:55 pm
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
Re: Open new file for output from Calc using macro
Posted: Wed Sep 21, 2011 3:21 pm
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.
Re: Open new file for output from Calc using macro
Posted: Wed Sep 21, 2011 6:17 pm
by Villeroy
Simply link your data.txt to a sheet in your spreadsheet document (Insert>SheetFromFile) and let your VB script exchange that file.
Re: Open new file for output from Calc using macro
Posted: Wed Sep 21, 2011 6:40 pm
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.