Excel VBA - Data Update
From: teyhuiyi (teyhuiyi.14zphq_at_excelforum-nospam.com)
Date: 04/20/04
- Next message: Paul B: "Re: Hide password in the InputBox"
- Previous message: Dave Peterson: "Re: OnKey trapping SHIFT+ALT+Spacebar? Possible?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 19 Apr 2004 21:33:28 -0500
I need some help..I am supposed to allow users to update the data from
the same file only once..everytime i run the program, this codes would
run by itself without checking for the previously updated data..
If MsgBox("You can only update a file ONCE!", vbOKOnly) = vbOK Then
Exit Sub
how do i check for the file names that is previously being updated?my
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 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) <> "" Then
If MsgBox("You can only update a file ONCE!", vbOKOnly) = vbOK
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..
--- Message posted from http://www.ExcelForum.com/
- Next message: Paul B: "Re: Hide password in the InputBox"
- Previous message: Dave Peterson: "Re: OnKey trapping SHIFT+ALT+Spacebar? Possible?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
