CODEWITHSUNDEEP
excelvbapower-automate
RookieTechie
RookieTechie

Reputation: 35

Updating the existing xlsx file from changes in xlsm file

I am using a macro-enabled workbook (xlsm) file to capture and record values. However, due to limitations with Power Automate for xlsm files, I have created a duplicate file in xlsx format. Currently, my approach involves repeatedly deleting and recreating the xlsx file with the same name. Unfortunately, this process generates a unique ID in Sharepoint each time the file is created, leading to referencing errors in my Power Automate flow.

Sub createXlsx()

    ' Path for the file to be placed at
    Dim path As String
    path = "C:\Users\user\Testing\"
    
    ' The name of the new file
    Dim newFile As String
    newFile = "Test.xlsx"

    Dim name As String
    name = path & newFile

    ' Check if the file already exists
    If Dir(path & newFile) <> "" Then
        Kill (path & newFile)
    End If

    Application.DisplayAlerts = False

    ' Create a new workbook
    Dim newWorkbook As Workbook
    Set newWorkbook = Workbooks.Add

    ' Copy the used range from the original worksheet to the new workbook
    Sheet1.UsedRange.Copy newWorkbook.Sheets(1).Range("A1")

    ' Save the new workbook as .xlsx format
    newWorkbook.SaveCopyAs path & newFile
    newWorkbook.Close SaveChanges:=False

    Application.DisplayAlerts = True

End Sub

I am seeking assistance to update the existing file instead of using the "kill" method to delete and create a new file. This approach would help me resolve the problem in Power Automate, as I am unsure how to obtain the newly created file each time and include it in the "List Row in a Table" action within my automated flow.

Upvotes: 0

Views: 278

Answers (2)

Ike
Ike

Reputation: 13024

You can update the workbook instead of creating a new one:

Sub updateXlsx()

    ' Path for the file to be placed at
    Dim path As String
    path = "C:\Users\user\Testing\"
    
    ' The name of the new file
    Dim targetFile As String
    targetFile = "Test.xlsx"

    Dim fullpathTargetFile As String
    fullpathTargetFile = path & targetFile

    'Application.DisplayAlerts = False

    ' open the workbook
    Dim wbTarget As Workbook
    Set wbTarget = Workbooks.Open(fullpathTargetFile)

    Dim wsTarget As Worksheet
    Set wsTarget = wbTarget.Worksheets(1)

    'delete current content
    wsTarget.UsedRange.Delete xlShiftUp

    ' Copy the used range from the original worksheet to the target workbook    
    sheet1.UsedRange.Copy wsTarget.Range("A1")
    wbTarget.Close SaveChanges:=True

    'Application.DisplayAlerts = True

End Sub

Upvotes: 1

CHill60
CHill60

Reputation: 1958

Use Workbook.Open to open the file See Microsoft Learn : Workbook.Open

You can copy the entire sheet over to the other workbook like this

ThisWorkbook.Sheets(1).Copy After:=Workbooks(NewWorkbook).Sheets(1)

Upvotes: 1

Related Questions