Macro/VBA - open any file which is available in a folder

[zainou]

So... basically, i have automated using a macro that will save a checklist in excel document based on customer's specific ID in a dedicated folder
at all times, the folder will only consist of 1 excel document

How do i write in macro telling to just go this destination and open any file (which is practically only one excel) regardless of its name (different ID)

 

[Saurabhj]

Hi,

if you have only one file. Use below code:

sub openMyFile()
Dim filePath as String
Dim WB as Workbook

filePath = "C:\myfiles\filename.xlsx"
set WB = Workbooks.Open (filePath)
End Sub

 

[zainou]

Hi,

if you have only one file. Use below code:

sub openMyFile()
Dim filePath as String
Dim WB as Workbook

filePath = "C:\myfiles\filename.xlsx"
set WB = Workbooks.Open (filePath)
End Sub

hi thank you for your reply
this doesnt resolve the issue of any name of the file
with your proposed solution i have to insert the specific name file in the macro which i couldnt do it because each customer's ID is different

 

[Saurabhj]

Hi,

Use below code:

Sub openTheFile()
   
    Dim file As String
    Dim filepath As String
    
    filepath = "C:\subfolder\"
    
    file = Dir(filepath)

    If (file <> "") Then
        Workbooks.Open filepath & "\" & file
    End If
End Sub

 

[zainou]

Hi,

Use below code:

Sub openTheFile()
  
    Dim file As String
    Dim filepath As String
   
    filepath = "C:\subfolder\"
   
    file = Dir(filepath)

    If (file <> "") Then
        Workbooks.Open filepath & "\" & file
    End If
End Sub

Hi Saurabhj

I thank you for your willingness to help me
May i get your further assistance whether am i incorating it correctly below?

Private Sub CommandButton1_Click()
    Dim wb As Workbook
    Set wb = Workbooks.Add
    ThisWorkbook.Sheets("Individual").Copy Before:=wb.Sheets(1)
    ThisWorkbook.Sheets("DD").Copy Before:=wb.Sheets(2)
    ThisWorkbook.Sheets("Value").Copy Before:=wb.Sheets(2)
    wb.SaveAs "C:\Users\User\Documents\Education\VBA\split save\" & Range("A1").Value & ".xlsl"
        filepath = "C:\Users\User\Documents\Education\VBA\split save\"
        file = Dir(filepath)
        If (file <> "") Then
            Workbooks.Open filepath & "\" & file
        End If
    Sheets("Individual").Select
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("L7").Select
    ActiveSheet.Shapes.Range(Array("CommandButton1")).Select
    Selection.Delete
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub
[CODE=vba]

but im getting an error at "  Columns("A:A").Select"
would you know why?

Im terribly sorry for the amateurish coding above

 

[Saurabhj]

Hi,

1. Can you please share what error are you getting ?
2. Make sure you are referring correct workbook to select the worksheet.
3. Instead of select, use Activate for sheet.

e.g.
wb.Sheets("Individual").Activate
Columns("A:A").Select

 

[zainou]

Hi,

1. Can you please share what error are you getting ?
2. Make sure you are referring correct workbook to select the worksheet.
3. Instead of select, use Activate for sheet.

e.g.
wb.Sheets("Individual").Activate
Columns("A:A").Select

Hi Saurabhj,

1. The error is 'Run time error 1004'

2. I want the last phase actions to take place on the newly opened excel

3. i followed your advice to use Activate. i encounter the same error.

 

[Saurabhj]

- Please check the sheet name.
- Is the sheet "Individual" exist in the workbook you opened??
- Activate the workbook in which Individual sheet is available.