Dowsey1977
09-28-2005, 01:52 AM
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
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