Question

How to save and close workbook open in VBA as passing input parameter

  • 10 January 2022
  • 1 reply
  • 20 views

Badge +2

I got a vba script from online in which i am passing input arg(Excel Path: my current workbook name) from AA to perform some action. Now I want to save my opened workbook which i pass as arg after vba execution.

 

Steps i used

Open Macro-> run Macro with passing input arg->close Macro.

 

Now i want to save and close the workbook which opened during vba function.

I tried using save and save as function but no luck. below vba code. After i save the worked i need to perform other logic too

 

Sub parse_data()

Sub Test(Excel_Path As String)

'Update by Extendoffice 2018/3/2

 

 

  Dim xRCount As Long

  Dim Wb As Workbook

  

 

  Dim xSht As Worksheet

 

  Dim xNSht As Worksheet

 

  Dim I As Long

 

  Dim xTRrow As Integer

 

  Dim xCol As New Collection

 

  Dim xTitle As String

 

  Dim xSUpdate As Boolean

  Set Wb = Workbooks.Open(Excel_Path)

  

  Set xSht = ActiveSheet

  xSht.Range("A1:Q1").AutoFilter field:=13, Criteria1:=xlFilterLastMonth, Operator:=xlFilterDynamic

  On Error Resume Next

 

  xRCount = xSht.Cells(xSht.Rows.Count, 2).End(xlUp).Row

 

  xTitle = "A1:Q1"

 

  xTRrow = xSht.Range(xTitle).Cells(2).Row

 

  For I = 2 To xRCount

 

    Call xCol.Add(xSht.Cells(I, 2).Text, xSht.Cells(I, 2).Text)

 

  Next

 

  xSUpdate = Application.ScreenUpdating

 

  Application.ScreenUpdating = False

 

  For I = 1 To xCol.Count

 

    Call xSht.Range(xTitle).AutoFilter(2, CStr(xCol.Item(I)))

 

    Set xNSht = Nothing

 

    Set xNSht = Worksheets(CStr(xCol.Item(I)))

 

    If xNSht Is Nothing Then

 

      Set xNSht = Worksheets.Add(, Sheets(Sheets.Count))

 

      xNSht.Name = CStr(xCol.Item(I))

 

    Else

 

      xNSht.Move , Sheets(Sheets.Count)

 

    End If

 

    xSht.Range("A" & xTRrow & ":A" & xRCount).EntireRow.Copy xNSht.Range("A1")

 

    xNSht.Columns.AutoFit

    

 

  Next

 

  xSht.AutoFilterMode = False

 

  xSht.Activate

 

  Application.ScreenUpdating = xSUpdate

 

  

  

End Sub

 

 

 


1 reply

Badge +3

@NagaSujitha Krishna​  - before End Sub line write as below

 

wb.Save

wb.Close

 

If this helps you please do Like & mark as "Best Answer" so it will be helpful for others as well. 

 

Best Regards,

Pradeep.

Reply