Help identfying workbook name

[termeric]

I would like to import 2 worksheets form another workbook but I am having some trouble toggling between the source and destination workbooks. How can I get the file name of the workbook that I am opening so that I can switch between the two and also close it at the end? I am repurposing this code form an earlier project where I had the source workbook information in one of the cells, but that is not available this time around. any thoughts on what I need to add to this?

thanks,

Sub Returner()

Dim tabname1 As String
Dim sheetname1 As String
Dim SaveName As String
Dim openfile As String

'On Error GoTo Errhandle            '<---- Diabled while testing

Application.DisplayAlerts = False
Application.AskToUpdateLinks = False

SaveName = ""
sheetname1 = "temp"                 '<---- Destination 1
sheetname2 = "temp2"                '<---- Destination 2
tabname1 = "IOC_Import"             '<---- Source 1
tabname2 = "Financial Summary EV4"  '<---- Source 2

'open the survey
'MsgBox "Pls select a survey to import", vbOKOnly
'openfile = Application.GetOpenFilename("Excel Files (*.xlsm),*.xlsm", , "Open a spreadsheet...")
openfile = Application.GetOpenFilename("Excel Files (*.xls*),*.xls*", , "Open a spreadsheet...")
Workbooks.Open FileName:=openfile, ReadOnly:=True

'SaveName = Range("a1").Value & ".xlsx"
SaveName = ThisWorkbook.Name        '<---- This gives me the destination not the source name

'copy the contents of the survey
    Sheets(tabname1).Select
    Cells.Select
    Selection.Copy
    Windows(WorkbookName).Activate
    Sheets(sheetname1).Activate
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    
    Windows(SaveName).Activate      '<---- Activate the Source file again
    
    Sheets(tabname2).Select
    Cells.Select
    Selection.Copy
    Windows(WorkbookName).Activate
    Sheets(sheetname2).Activate
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    
'close the survey

    Windows(SaveName).Activate      '<---- Close the Source file
    ActiveWindow.Close


Application.DisplayAlerts = True
Application.AskToUpdateLinks = True

'Errhandle:
Exit Sub


End Sub

Public Function WorkbookName() As String

    'Finds the name of this spreadsheet
    WorkbookName = ThisWorkbook.Name

End Function

 

[Joe4]

How can I get the file name of the workbook that I am opening so that I can switch between the two and also close it at the end?

Create a workbook object, and set it after opening the workbook.

Here is a simple little example that shows you how you can set that, bounce back and forth between workbooks, and even close them.

Sub Test()

    Dim openfile As String
    Dim oldwb As Workbook
    Dim newwb As Workbook

'   Set oldwb equal to the ActiveWorkbook where macro resides
    Set oldwb = ActiveWorkbook
    
'   Open new workbook
    openfile = Application.GetOpenFilename("Excel Files (*.xls*),*.xls*", , "Open a spreadsheet...")
    Workbooks.Open Filename:=openfile, ReadOnly:=True

'   Set newwb equal to the new workbook just opened
    Set newwb = ActiveWorkbook
    
'   Bounce back to oldwb
    oldwb.Activate
    
'   Close newwb
    newwb.Close

End Sub

 

[Norie]

Try this.

Sub Returner()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim tabname1 As String
Dim sheetname1 As String
Dim SaveName As String
Dim openfile As String

    'On Error GoTo Errhandle            '<---- Diabled while testing

    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False

    SaveName = ""
    sheetname1 = "temp"                 '<---- Destination 1
    sheetname2 = "temp2"                '<---- Destination 2
    tabname1 = "IOC_Import"             '<---- Source 1
    tabname2 = "Financial Summary EV4"  '<---- Source 2

    wbDst = ThisWorkbook

    'open the survey
    'MsgBox "Pls select a survey to import", vbOKOnly
    'openfile = Application.GetOpenFilename("Excel Files (*.xlsm),*.xlsm", , "Open a spreadsheet...")
    openfile = Application.GetOpenFilename("Excel Files (*.xls*),*.xls*", , "Open a spreadsheet...")
    Set wbSrc = Workbooks.Open(Filename:=openfile, ReadOnly:=True)

    'SaveName = Range("a1").Value & ".xlsx"
    SaveName = ThisWorkbook.Name        '<---- This gives me the destination not the source name

    'copy

    wbSrc.Sheets(tabname1).Cells.Copy wbDst.Sheets(sheetname1).Range("A1")
    wbSrc.Sheets(tabname2).Cells.Copy wbDst.Sheets(sheetname2).Range("A1")
    wbSrc.Close

    Application.DisplayAlerts = True
    Application.AskToUpdateLinks = True

    'Errhandle:
    Exit Sub

End Sub

 

[termeric]

Thanks Norie,

I get an error on line "wbDst = ThisWorkbook"

Should that be ThisWorkbook.Name?

 

[termeric]

I got it, Set WbDst = thisworkbook

Thanks for the help!

 

[Norie]

Oops, sorry about that.