Excel VBA - Updating of data problem
From: teyhuiyi (teyhuiyi.14znhh_at_excelforum-nospam.com)
Date: 04/20/04
- Next message: scottnshelly: "Re: simple question regarding formatting in a cell with a formula"
- Previous message: gav meredith: "copy data range"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 19 Apr 2004 20:50:07 -0500
I have another problem..the codes that i am using now allows the user to
choose whether they wants to overwrite the previous data. When the user
clicks on No, the program will exit from the updating without any
actions taken. But when the user clicks on Yes, the user will not
search for the file that is previously being updated but jus update the
data into new rows. And the program doesn't check for the files that
the user wants to update and immediately executes this code:
*If MsgBox("You have updated this file before. Are you sure you want to
overwrite the previous date?", vbYesNo) = vbNo Then Exit Sub*
I hope that someone out there would be able to help me solve this
problem..Thanks..
My whole code goes like this:
[B]Option Explicit
Sub ImportRangeFromWB(SourceSheet As String, _
SourceAddress As String, PasteValuesOnly As Boolean, _
TargetWB As String, TargetWS As String, TargetAddress As String)
'Imports the data in
Workbooks(SourceFile).Worksheets(SourceSheet).Range(SourceAddress)
'to Workbooks(TargetWB).Worksheets(TargetWS).Range(TargetAddress)
'Replaces existing data in Workbooks(TargetWB).Worksheets(TargetWS)
'without prompting for confirmation
'Example
'ImportRangeFromWB "C:\FolderName\TargetWB.xls", _
"Sheet1", "A1:E21", True, ThisWorkbook.Name, "ImportSheet", "A3"
Dim SourceFile As String
Dim SourceWB As Workbook, SourceWS As String, SourceRange As Range
Dim TargetRange As Range, A As Integer, tString As String
Dim i As Integer
Dim CellValue As String
'validate the input data if necessary
SourceFile = Application.GetOpenFilename("Excel Files,*.xls")
If Dir(SourceFile) = "" Then Exit Sub 'SourceFile doesn't exist
If Dir(SourceFile) = Dir(SourceFile) Then
If MsgBox("You have updated this file before. Are you sure you
want to overwrite the previous date?", vbYesNo) = vbNo Then Exit Sub
End If
Set SourceWB = Workbooks.Open(SourceFile, True, True)
Application.StatusBar = "Reading data from " & SourceFile
Workbooks(TargetWB).Activate
Worksheets(TargetWS).Activate
'perform input
Application.ScreenUpdating = False
Set TargetRange = Range(TargetAddress).Cells(1, 1)
Set SourceRange =
SourceWB.Worksheets(SourceSheet).Range(SourceAddress)
For A = 1 To SourceRange.Areas.Count
SourceRange.Areas(A).Copy
If SourceRange.Areas.Count > 1 Then
Set TargetRange = _
TargetRange.Offset(TargetRange.Areas(A).Rows.Count, 1)
End If
i = 5
For i = 5 To 5000
CellValue = Cells(i, 3)
If CellValue = "" Then
Set TargetRange = Cells(i, 3)
i = 5000
End If
Next i
If PasteValuesOnly Then
TargetRange.PasteSpecial xlPasteValues
TargetRange.PasteSpecial xlPasteFormats
Else
TargetRange.PasteSpecial xlPasteAll
End If
Application.CutCopyMode = False
Next A
'clean up
'Set SourceRange = Nothing
'Set TargetRange = Nothing
Range(TargetAddress).Cells(1, 1).Select
SourceWB.Close False
Set SourceWB = Nothing
Application.StatusBar = False
End Sub[\B]
--- Message posted from http://www.ExcelForum.com/
- Next message: scottnshelly: "Re: simple question regarding formatting in a cell with a formula"
- Previous message: gav meredith: "copy data range"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
