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
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