Open file with VBA

[jevi]

Hi All,

I registered this macro but I would like to Open automatically the file "Data File.xlsx" and not open it manually. But his macro it sounds need this file open and not closed in the folder.
Thank you,

' Macro8 Macro
'

'
    Columns("A:E").Select
    Selection.Copy
    Windows("File1.xlsm").Activate
    Columns("A:E").Select
    ActiveSheet.Paste
    Windows("Data File.xlsx").Activate
    ActiveWindow.Close
    Range("A1:E1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$E$100000").AutoFilter Field:=3, Criteria1:="DIR"
    Columns("A:E").Select
    Selection.Copy
    Sheets("Imports").Select
    Columns("A:E").Select
    ActiveSheet.Paste
    Sheets("PIVOT").Select
    Range("B8").Select
    Application.CutCopyMode = False
    ActiveWorkbook.RefreshAll
    Sheets("Database").Select
    Range("A1").Select
    Selection.AutoFilter
    ActiveWorkbook.Save
End Sub

 

[DanteAmor]

Try this:
Only you adjust the names of the sheets.

Sub Macro8()
  Dim wb1 As Workbook, wb2 As Workbook
  Dim sh1 As Worksheet
  Dim sPath As String, sFile As String
  
  Set wb1 = ThisWorkbook
  'Change "Database" to the name of your sheet where you paste the information.
  Set sh1 = wb1.Sheets("Database")
  
  sPath = wb1.Path & "\"
  sFile = "Data File.xlsx"
  
  Set wb2 = Workbooks.Open(sPath & sFile)
  'Change "Sheet1" to the name of the sheet in the "Data file.xlsx" book.
  wb2.Sheets("Sheet1").Columns("A:E").Copy sh1.Range("A1")
  wb2.Close False
  '
  sh1.Range("A1:E1").AutoFilter
  sh1.Range("A1:E" & sh1.Range("C" & Rows.Count).End(3).Row).AutoFilter Field:=3, Criteria1:="DIR"
  sh1.Columns("A:E").Copy Sheets("Imports").Range("A1")
  '
  Sheets("PIVOT").Select
  Range("B8").Select
  wb1.RefreshAll
  Sheets("Database").Select
  Range("A1").Select
  Selection.AutoFilter
  wb1.Save
End Sub

 

[jevi]

Hi DanteAmor,
It worked as a treat . Thank you so much). But as I will use this type of VBA in other files, I would like to know if the code that I copy from yours below is the one who permits me to not have the file open, but it opens it automatically?

wb2.Close False - and this one permits me to close it. It looks so easy when you know it

Dim wb1 As Workbook, wb2 As Workbook ( i should write it in the beggining of a VBA)

 sPath = wb1.Path & "\"
  sFile = "Data File.xlsx"
  
  Set wb2 = Workbooks.Open(sPath & sFile)

 

[jevi]

Just one small problem but If I can automate that as is daily would be great. I just find out now as it is a new report....the file
Data File.xlsx, I download everyday from a procedure of ours doesn't keep the same sheet, it changes sheet "sampleName (23)", and yesterdy was "sampleName (22)", so it sound everytime I download it gives +1...is there a way to tell to get the info from the first sheet (or the only sheet) as this file as only one sheet of data.

Thank you and sorry....

 

[DanteAmor]

.is there a way to tell to get the info from the first sheet

If you mean this file, change "Sheet1" to 1

  Set wb2 = Workbooks.Open(sPath & sFile)
  'Change "Sheet1" to the name of the sheet in the "Data file.xlsx" book.
  wb2.Sheets(1).Columns("A:E").Copy sh1.Range("A1")
  wb2.Close False

 

[jevi]

Super thank you..it worked great .

 

[DanteAmor]

Im glad to help you, thanks for the feedback.