PDA

View Full Version : Solved: Clear sheet only if File is selected



JimS
04-25-2011, 01:18 PM
I have a workbook with several worksheets that the user can update using some buttons.

I use the VBA below to provide the user with the option of updating the worksheets.

This work fine.

I clear the contains of the destination worksheet early in the routine.

I was wondering if this could be modified so that the orginal worksheet (Sheet21 in this example) only gets cleared if the user actually selects a source file with the DialogBox.

If no file is selected then the original data is still intact.

Can this be done?

Thanks...

JimS


Sub Import_File1()

Dim DestBook As Workbook, SourceBook As Workbook
Dim DestCell As Range
Dim RetVal As Boolean

' Turn off screen updating.
Application.ScreenUpdating = False

' Unhide and select destination sheet
Sheet21.Visible = True
Sheet21.Select

' Added to clear Sheet before Import
Cells.Select
Selection.ClearContents
Range("A1").Select

' Set object variables for the active book and active cell.
Set DestBook = ActiveWorkbook
Set DestCell = ActiveCell

' Show the Open dialog box.
RetVal = Application.Dialogs(xlDialogOpen).Show("*.*")

' If Retval is false (Open dialog canceled), exit the procedure.
If RetVal = False Then Exit Sub

' Set an object variable for the workbook containing the text file.
Set SourceBook = ActiveWorkbook

' Copy the contents of the entire sheet containing the text file.
Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy

' Activate the destination workbook and paste special the values from the text file.
DestBook.Activate
Sheet21.Select
DestCell.PasteSpecial Paste:=xlPasteFormats
DestCell.PasteSpecial Paste:=xlValues

' Close the book containing the text file.
Application.DisplayAlerts = False
SourceBook.Close False
Application.DisplayAlerts = True

End Sub

austenr
04-25-2011, 04:54 PM
try defining a string variable, then right after the dialogue box if a file is selected, set the string variable to true and if so clear the sheet otherwise error out.