CODEWITHSUNDEEP
vbaexcel
user2273278
user2273278

Reputation: 1315

VBA Open any workbook

I have a macro that opens a spreadsheet from a specific folder and saves the output to a worksheet called Sheet1 in another workbook. The macro works if the file name is called "MyFile.xls" but i would like it to be able to run on any file name but it must have a "Book2" worksheet.

Here is my code:

Dim source As Workbook
Dim output As Workbook
Dim sourceSheet as WorkSheet
Dim outputSheet as WorkSheet
Dim file As String
file = "C:\Spreadsheets\MyFile.xls"  'I would like it to handle any files from any location'

Set output = ThisWorkBook
output.Activate

If Len(Dir$(file)) > 0 Then
    Set source = workbooks.Open(file)

Set sourceSheet = source.Worksheets("Book2") 'Must only run if the sheet is called Book2'
Set outputSheet = output.Worksheets("Sheet1") 'Saves sheets into a new sheet called Sheet1'

End Sub

Upvotes: 2

Views: 2137

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

Is this what you are trying? (TRIED AND TESTED)

Sub Sample()
    Dim source As Workbook, output As Workbook
    Dim sourceSheet As Worksheet, outputSheet As Worksheet

    Dim File

    '~~> Show a dialog to open any excel file
    File = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")

    If File = False Then Exit Sub

    Set output = ThisWorkbook

    If Len(Dir$(File)) > 0 Then
        Set source = Workbooks.Open(File)

        '~~> Error check to see if the workbook has that sheet
        On Error Resume Next
        Set sourceSheet = source.Worksheets("Book2")

        If Err.Number = 0 Then

            Set outputSheet = output.Worksheets("Sheet1")
            '
            '~~> Rest of your code
            '
        Else
            MsgBox "Not found"
            source.Close SaveChanges:=False
        End If
        On Error GoTo 0
    End If
End Sub

Upvotes: 1

Related Questions