Create a new file (copy file to new file with macro)

topo_gigio · January 30, 2025, 10:10am

hi, i need a simple macro but with the recorder there is no way.
i have to copy the range a1:cc1 from sheet1 to sheet2, then i have to create a copy of sheet 2 in a new file and save it on the desktop and close it.
thanks in advance for the help.

fpy · January 30, 2025, 11:44am

AI says :

sub CopyRangeAndSaveSheet()

  Dim oDoc As Object, oSheet1 As Object, oSheet2 As Object
  Dim oRange As Object, oNewDoc As Object, oNewSheet As Object
  Dim sDesktopPath As String, sFilePath As String

  ' Get the current document and sheets
  oDoc = ThisComponent
  oSheet1 = oDoc.Sheets.getByName("Sheet1") ' Adjust sheet name if needed
  oSheet2 = oDoc.Sheets.getByName("Sheet2") ' Adjust sheet name if needed

  ' Copy the range A1:CC1 from Sheet1 to Sheet2
  oRange = oSheet1.getCellRangeByName("A1:CC1")
  oSheet2.getCellRangeByName("A1:CC1").setDataArray(oRange.getDataArray()) 'Using setDataArray for efficiency

  ' Create a new document
  oNewDoc = StarDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, Array())

  ' Copy Sheet2 to the new document
  oNewSheet = oNewDoc.Sheets.getByName("Sheet1") 'Gets the first sheet in the new document
  oNewDoc.Sheets.moveByName("Sheet2", oNewSheet) 'Moves Sheet2 to be the first sheet
  oNewDoc.Sheets.removeByName(oNewSheet.Name) 'Removes the original first sheet (now second)

  ' Get the desktop path (more complex in LibreOffice)
  sDesktopPath = Environ("HOME") & "/Desktop/" 'Most common for Linux/macOS; might need adjustment for Windows

  ' Construct the file path (using date and time for unique name)
  sFilePath = sDesktopPath & "CopiedSheet_" & Format(Now, "YYYYMMDDHHMMSS") & ".ods"

  ' Save the new document
  oNewDoc.storeAsURL(ConvertToURL(sFilePath), Array())

  ' Close the new document
  oNewDoc.close(True) ' True means save, but we already saved, so it's effectively just closing

  MsgBox("Sheet copied and saved to: " & sFilePath, 0, "Success")

topo_gigio · January 30, 2025, 1:02pm

(post deleted by author)

topo_gigio · January 30, 2025, 1:28pm

(post deleted by author)

topo_gigio · January 30, 2025, 2:09pm

Perfect, thank you

karolus · January 30, 2025, 3:00pm

NI says:

# python
from datetime.datetime import now
from pathlib import Path

def main

    doc = XSCRIPTCONTEXT.getDocument()
    data = doc.Sheets[0]["A1:CC1"].DataArray
    
    desktop = XSCRIPTCONTEXT.getDesktop()
    new_doc = desktop.loadComponentFromURL("private:factory/scalc",
                                           "_blank",
                                           0,
                                           (),)
    new_doc.Sheets[0]["A1:CC1"].DataArray = data
    
    url = (Path.home() / "Desktop" / f"copy_{now():%y_%m_%d%H%M%S}.ods").as_uri()
    
    new_doc.storeAsURL( url, (),)
    new_doc.close(True)

or even with basic:

Sub Main

	sheet = thisComponent.Sheets(0) 'first Sheet
	data = sheet.getCellRangeByName("A1:CC1").DataArray

	new_doc = StarDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, Array())
	new_doc.Sheets(0).getCellRangeByName("A1:CC1").DataArray = data

	folder = Environ("HOME") & "/Desktop/" 

  ' Construct the file path (using date and time for unique name)
	file_path = folder & "CopiedSheet_" & Format(Now, "YYYYMMDDHHMMSS") & ".ods"

  ' Save the new document
 	new_doc.storeAsURL(ConvertToURL( file_path), Array())

  ' Close the new document
 	new_doc.close(True) ' True means save, but we already saved, so it's effectively just closing

 	MsgBox("Sheet copied and saved to: " & file_path, 0, "Success")


End Sub