Sleeper: Importing a worksheet

[Dowsey1977]

Hi,

I'm using the below code to import a worksheet from a workbook that a user browses for.

I want to change the reference to "Combined Form 6 Template macro2.xls" (near the end) so that it closes the workbook just imported, whatever it is called. In the case where a workbook with a different name is imported this macro won't work, so I want to say something like...
wbk = imported workbook, so I can change


Windows("Combined Form 6 Template macro2.xls").Activate
ActiveWindow.Close

To


Windows("wbk").Activate
ActiveWindow.Close


Sub Test()
'Macro Purpose: To test the GetOpenFilenameFrom function
Dim sWBToOpen As Variant
Dim Answer
sWBToOpen = GetOpenFilenameFrom(Range("A3").Value)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If sWBToOpen = False Then
End
Else
If Not sWBToOpen = False Then Workbooks.Open (sWBToOpen)
Sheets("FORM 6").Select
Sheets("FORM 6").Copy Before:=Workbooks("NEW FORM 6.xls").Sheets(2)
Sheets("FORM 6").Select
Range("A72").Select
Answer = MsgBox("The Form 6 sheet has successfully been imported and the data is ready to copy." & vbCrLf & vbCrLf & _
"Click 'Ok' to copy the data and 'Cancel' to view Form 6 before copying", vbOKCancel, "Payment Information")
If Answer = vbOK Then
Call WhichTransaction
Sheets("Form 6").Select
ActiveSheet.Delete
Sheets("Control Sheet").Select
Else
Sheets("FORM 6").Select
End If
Windows("Combined Form 6 Template macro2.xls").Activate
ActiveWindow.Close
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Hope that makes sense...any help appreciated.

Thanks in advance,
Me

[Bob Phillips]

Sub Test()
'Macro Purpose: To test the GetOpenFilenameFrom function
Dim sWBToOpen As Variant
Dim Answer
sWBToOpen = GetOpenFilenameFrom(Range("A3").Value)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If sWBToOpen = False Then
End
Else
If Not sWBToOpen = False Then Workbooks.Open (sWBToOpen)
Sheets("FORM 6").Select
Sheets("FORM 6").Copy Before:=Workbooks("NEW FORM 6.xls").Sheets(2)
Sheets("FORM 6").Select
Range("A72").Select
Answer = MsgBox("The Form 6 sheet has successfully been imported and the data is ready to copy." & vbCrLf & vbCrLf & _
"Click 'Ok' to copy the data and 'Cancel' to view Form 6 before copying", vbOKCancel, "Payment Information")
If Answer = vbOK Then
Call WhichTransaction
Sheets("Form 6").Select
ActiveSheet.Delete
Sheets("Control Sheet").Select
Else
Sheets("FORM 6").Select
End If
Workbooks(sWBToOpen).Close savechanges:=False
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

[Dowsey1977]

Many thanks for this code, but unfortunately it still brings up an error message and doesn't close the opened spreadsheet...any other ideas?

[Norie]

Does this work?


Sub Test()
'Macro Purpose: To test the GetOpenFilenameFrom function
Dim sWBToOpen As Variant
Dim wb As Workbook
Dim Answer

sWBToOpen = GetOpenFilenameFrom(Range("A3").Value)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

If sWBToOpen = False Then
End

Else
Set wb = Workbooks.Open(sWBToOpen)
wb.Sheets("FORM 6").Copy Before:=Workbooks("NEW FORM 6.xls").Sheets(2)

Answer = MsgBox("The Form 6 sheet has successfully been imported and the data is ready to copy." & vbCrLf & vbCrLf & _
"Click 'Ok' to copy the data and 'Cancel' to view Form 6 before copying", vbOKCancel, "Payment Information")
If Answer = vbOK Then
Call WhichTransaction
wb.Sheets("Form 6").Delete
wb.Sheets("Control Sheet").Select
Else
wb.Sheets("FORM 6").Select
End If

wb.Close

End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
By the way what is GetOpenFileNameFrom?