Excel VBA - Data Update

T

teyhuiyi

I need some help..I am supposed to allow users to update the data fro
the same file only once..everytime i run the program, this codes woul
run by itself without checking for the previously updated data..

If MsgBox("You can only update a file ONCE!", vbOKOnly) = vbOK The
Exit Sub

how do i check for the file names that is previously being updated?m
codes now doesn't allow me to do so..this is how my codes goes:

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 i
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) <> "" Then
If MsgBox("You can only update a file ONCE!", vbOKOnly) = vbO
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

I hope someone out there can help me with this..thank you.
 
Top