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

zainou

New Member
Joined
Jan 29, 2021
Messages
5
Office Version
  1. 365
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)
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

if you have only one file. Use below code:

VBA Code:
sub openMyFile()
Dim filePath as String
Dim WB as Workbook

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

zainou

New Member
Joined
Jan 29, 2021
Messages
5
Office Version
  1. 365
Hi,

if you have only one file. Use below code:

VBA 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

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Use below code:

VBA 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

New Member
Joined
Jan 29, 2021
Messages
5
Office Version
  1. 365

ADVERTISEMENT

Hi,

Use below code:

VBA 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?

VBA Code:
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

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

New Member
Joined
Jan 29, 2021
Messages
5
Office Version
  1. 365
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

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
- Please check the sheet name.
- Is the sheet "Individual" exist in the workbook you opened??
- Activate the workbook in which Individual sheet is available.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,524
Messages
5,625,309
Members
416,092
Latest member
dodovisk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top