[Solved] Save range as .xls

[sourav16]

Dear Team,

In my Apache OpenOffice Calc document, comprising three sheets named Sheet1, Sheet2, and Sheet3, I aim to extract data exclusively from "Sheet1" within the range "A1:F20." Subsequently, I intend to save this extracted data as "File.xls" in the directory "D:\File" using OpenOffice Basic. Could you please provide the appropriate code for this task?


I have used the below code:

Code: Select all

Sub Macro1
    Call UnlockRangeInObservationSheet
    
    ' ----------------------------------------------------------------------
    ' Define variables
    Dim document As Object
    Dim dispatcher As Object
    
    ' ----------------------------------------------------------------------
    ' Get access to the document
    document = ThisComponent.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    
    ' ----------------------------------------------------------------------
    ' Set up parameters to navigate to a specific cell range in "Observation_Sheet"
    Dim args1(0) As New com.sun.star.beans.PropertyValue
    args1(0).Name = "ToPoint"
    args1(0).Value = "'Observation_Sheet'.$A$1:$F$20"
    
    'dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    
    ' ----------------------------------------------------------------------
    ' Set up parameters to export "Observation_Sheet" to an Excel file
    Dim args2(8) As New com.sun.star.beans.PropertyValue
    Dim Cel As Object
    Dim Werkblad As Object
    Dim Werkmap As Object
    
    Werkmap = ThisComponent
    Werkblad = Werkmap.Sheets.getByName("Tracker")
    Cel = Werkblad.getCellByPosition(0, 0) 'A9
    Path = ThisComponent.Sheets.getByName("Tracker").getCellByPosition(0, 1).String 'J5
    
    If Path = "" Then
        MsgBox "Please mention Sanction Date"
    Else
        args2(0).Name = "URL"
        args2(0).Value = Path & Cel.getString() & ".xls"
        args2(1).Name = "FilterName"
        args2(1).Value = "MS Excel 97"
        args2(2).Name = "FilterOptions"
        args2(2).Value = "MS Excel 97"
        args2(3).Name = "Overwrite"
        args2(3).Value = True
        args2(4).Name = "SelectedSheets"
        args2(4).Value = True ' Export only selected sheets
        
        ' Change the property from "Selection" to "Table"
        args2(5).Name = "Table"
        Dim sheetList(0) As Variant
        sheetList(0) = "Observation_Sheet" ' Specify the sheet to export
        args2(5).Value = sheetList
        
        dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args2())
        
        ' ----------------------------------------------------------------------
        ' Close the exported document in the background
        Dim exportedDocument As Object
        exportedDocument = StarDesktop.loadComponentFromURL(args2(0).Value, "_blank", 0, Array())
        exportedDocument.close(True)
    End If
    
    Call LockRangeInObservationSheet
End Sub

But it is working as following:
1. It extract all the sheets
2. It closes the current file and opened the new file


What need to be done. If possible reply as soon as possible.

Thank You
Sourav Sarkar

[Hagar Delest]

Please read the Survival Guide for the forum.
First I removed the green tick mark you had put in your post; it is for solved topic only.
I also added the BBcode for code in your post.
This is not a customer service, we help on our free time. Thus speed for replies may vary depending on who is available.

[MrProgrammer]

I aim to extract data exclusively from "Sheet1" within the range "A1:F20." Subsequently, I intend to save this extracted data as "File.xls" in the directory "D:\File" using OpenOffice Basic.

First, ensure any changes which you have made to your file have been saved.
Then record a macro which saves A1:F20 in XLS format.
• Tools → Macros → Record
• Using the Name Box of the Formula Bar select Sheet1.E1:AMJ1048576
• Edit → Delete Cells   Columns you don't want to save are gone
• Using the Name Box of the Formula Bar select A21:AMJ1048576
• Edit → Delete Cells   Rows you don't want to save are gone
• File → Save As → File type → Microsnot Excel (.xls) → Specify location and file name → OK
• Stop Recording
Give your macro a name and save it. It should take less than a minute to record this.
If you want to continue working with your original file, just open the document you saved (the line in bold).

[Tutorial] Favorite Recorded Calc Macros

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[sourav16]

Please read the Survival Guide for the forum.
First I removed the green tick mark you had put in your post; it is for solved topic only.
I also added the BBcode for code in your post.
This is not a customer service, we help on our free time. Thus speed for replies may vary depending on who is available.

I am extremely sorry if my post make any inconvenience. and Thank You are your clarification.

[sourav16]

I aim to extract data exclusively from "Sheet1" within the range "A1:F20." Subsequently, I intend to save this extracted data as "File.xls" in the directory "D:\File" using OpenOffice Basic.

First, ensure any changes which you have made to your file have been saved.
Then record a macro which saves A1:F20 in XLS format.
• Tools → Macros → Record
• Using the Name Box of the Formula Bar select Sheet1.E1:AMJ1048576
• Edit → Delete Cells   Columns you don't want to save are gone
• Using the Name Box of the Formula Bar select A21:AMJ1048576
• Edit → Delete Cells   Rows you don't want to save are gone
• File → Save As → File type → Microsnot Excel (.xls) → Specify location and file name → OK
• Stop Recording
Give your macro a name and save it. It should take less than a minute to record this.
If you want to continue working with your original file, just open the document you saved (the line in bold).

[Tutorial] Favorite Recorded Calc Macros

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

Really Thank You for your prompt reply , but my query is not solve. I want to only extract a specific sheet. Then what need to be done. Can you suggest any other process.

[MrProgrammer]

… comprising three sheets named Sheet1, Sheet2, and Sheet3 …
I want to only extract a specific sheet.

Record using the Name Box to switch to Sheet2.A1 and then Edit → Sheet → Delete. Repeat for Sheet3. Then do the steps I suggested. The result will be a file in XLS format with a single sheet containing the specified range. The File → Save As operation means that your original document is not changed, which is why you must ensure it has been saved before running the macro and why you must re-open the original document if you want to continue using it.

I'm sure someone could write a macro to save a specified range in a specified sheet to another file. But I am not offering to do that. You need to decide if you will use a simple recorded macro that achieves 95% of the goal or if you want to spend a week or so learning how to write macros and use the complex API. And you can try searching the forum to see if other topics about this have been created. You can search as well as I can and have the advantage that you know what solution will work best for you. Or look at OpenOffice.org Macros Explained to see if it addresses this subject.

If you don't want OpenOffice to create three sheets for new documents, read [Solved] Create new spreadsheets with only Sheet1. This avoids having to delete two empty sheets if they are seldom used. If needed, you can create them with Insert → Sheet.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.