Solved: VBA Importing a sheet from a closed excel workbook

[Chris_DP]

Hello LeoLee, assign the following code for using import. I have used a little different version for my requirements. So I'd to tweak it little. I have used a function for checking existence of a specific sheet.

Sub ImportSheet()
Dim sImportFile As String, sFile As String
Dim sThisBk As Workbook
Dim vfilename As Variant
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set sThisBk = ActiveWorkbook
sImportFile = Application.GetOpenFilename( _
FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Open Workbook")
If sImportFile = "False" Then
   MsgBox "No File Selected!"
   Exit Sub
   Else
   vfilename = Split(sImportFile, "\")
   sFile = vfilename(UBound(vfilename))
   Application.Workbooks.Open Filename:=sImportFile
   Set wbBk = Workbooks(sFile)
   With wbBk
      If SheetExists("Raw_Data") Then
         Set wsSht = .Sheets("Raw_Data")
         wsSht.Copy before:=sThisBk.Sheets("Sheet1")
         Else
         MsgBox "There is no sheet with name :Raw_Data in:" & vbCr & .Name
      End If
      wbBk.Close SaveChanges:=False
   End With
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Private Function SheetExists(sWSName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets(sWSName)
If Not ws Is Nothing Then SheetExists = True
End Function

Hi shrivallabha,

Your code is almost perfect for me. I would just like to know how do I make the macro select a file path that is generated on the active worksheet, for example in cell A1, instead of selecting it manually. My Workbooks all have similar names, except for month changes in the name. I can find and replace this easily enough, but I want the macro to select the file path that is generated in the active worksheet.

Thanks in advance!