Perhaps the problem was with the previous message..
I'll try it without and see how it goes:
OK...
I think I'm going to answer your questions in reverse order....
--------------------
Option Explicit
--------------------
Using this AS THE FIRST LINE OF EACH MODULE tells the compiler that all
variables have to be "declared" (using Dim, Public, Global, etc.)
Without this, a variable doesn't HAVE to appear in a Dim statement.
The compiler will "automatically" declare the variable the first time it is
used.
This can be dangerous because if you're using a variable called "YearValue"
and in one place, you accidentally call it "YearValeu", the compiler will not
recognize that as a problem. It will simply create a new variable.
"Option Explicit" tells the compiler that all variables have to be "Explicitly"
declared.
----------------------------------------------------------
"The DIM statements do not have DATA TYPES "
----------------------------------------------------------
Data Types are not REQUIRED.
If not specified, the compiler defaults to type "Variant".
This only becomes a problem if the value being stored in the variable
is sometimes a string, and other times numeric.
Once a value is stored in the variable, the data type is set to the type of
data.
That is: YearValue = 2012 will define it as an Integer,
while YearValue = "2012" will define it as a String.
again, this isn't a problem unless you're NEEDING it as a number in
order to add/subtract from it.
----------------------------------------------------------
"The variable LOAD_DICT_DATA is not in any DIM statements"
----------------------------------------------------------
Load_Dict_Data is not a variable, it is a FUNCTION.
----------------------------------------------------------
"I see that there is a FUNCTION used. "
"Is that new MACRO?"
"Is that coded inside a MACRO?"
----------------------------------------------------------
The term "Macro" is actually inappropriate for VBA programming (mostly)
It refers to:
1. A single, user-defined command that is part of an application and executes a
series of commands.
2. A shorthand representation for a number of lines of code.
Yet, you'll notice that the work "Macro" doesn't actually appear in the VBA
Editor.
If you choose to "record a macro", VBA calls the it a "Sub" (short for
"Subroutine")
Another type of "macro" is called a "Function".
The main difference between the two is that you cannot "pass" a value to a
Subroutine,
nor can you "return" a value from a Sub. But you can from a Function.
For instance... throughout a lengthy subroutine, you periodically have
to modify a variable to ensure that it always is (4) characters long (prefixed
with "0").
Rather than repeat this code whenever you need to use it, you can create a
Function:
Function Pad4(tValue as string)
while len(tValue) < 4
tValue ="0"&tValue
wend
Pad4 = tValue
End Function
you can then use something like:
WeekValue = Pad4(WeekValue)
your result is that WeekValue will now be 4 characters long (i.e. "0025")
When writing Macros (subroutines) of significant length, it is common practice
to place separate "activities" in their own subs of functions.
Like: one function for reading existing data from a spreadsheet, a separate
function for reading new data from a CSV file.
Another benefit is that some of these "subs" cannot be run independently.
They MUST be called from within another sub.
(it doesn't make sense to read the NEW data until after you're read the EXISTING
data)
When you select the list of "Macros" to run from the Excel icon,
functions do not appear in the list.
so, in one of my applications that have 50 FUNCTIONS, there is only ONE that is
available to be executed!
----------------------------------------------------------
Can these macros be stored anywhere?
----------------------------------------------------------
Where macros are stored depends on how they are to be used.
macros stored in a Sheet module only apply to the specific sheet.
These should be stored in a "Standard" module.
----------------------------------------------------------
"I receive the new data as an CSV file. "
"How do I set "yearvalue" and "weekvalue"?"
----------------------------------------------------------
Since a CSV file is a Comma Separated file,
then I would open the file,
read each line,
"split" the line using the "," delimiter
Set the YearValue and WeekValue based on the position in the string
Like:
----------------------------------------------------------
"What happens if the search does not find any thing? "
"How do I check for that?"
----------------------------------------------------------
I went ahead and wrote the "Add/Update" function (see below)
The only things you should have to change is:
MasterSheetName should be set to the sheet name that contains your "Master Data"
NewDataFile should be set to the name of the CSV file.
----------------------------------------------------------
----------------------------------------------------------
Option Explicit
Public Dict_Data
Public MasterSheetName
Sub Auto_Open()
Dim stat, nRow
Dim NewDataFile
'--------------------------------------
MasterSheetName = "Master_Data"
NewDataFile = "C:\temp\NewData.csv"
'--------------------------------------
stat = Load_Dict_Data
stat = Read_New(NewDataFile)
End Sub
Function Load_Dict_Data()
Dim nRows, R, wRecord, stat
Set Dict_Data = CreateObject("Scripting.Dictionary")
stat = Dict_Data.RemoveAll
nRows =
Application.WorksheetFunction.CountA(Sheets(MasterSheetName).Range("A1:A1000000"))
For R = 2 To nRows
wRecord = Sheets(MasterSheetName).Cells(R, "A").Value _
& "_" & Sheets(MasterSheetName).Cells(R, "B").Value
If (Not Dict_Data.exists(wRecord)) Then
Dict_Data.Add wRecord, R
End If
Next R
Load_Dict_Data = Dict_Data.Count
End Function
Function Read_New(tDataFile)
Dim nRows, TextLine, TxtArray
Dim nRecord, DataRow
nRows =
Application.WorksheetFunction.CountA(Sheets(MasterSheetName).Range("A1:A1000000"))
'---- Open file -------------
Open tDataFile For Input As #1
'---- Loop until end of file ----------
Do While Not EOF(1)
'---- Read line into variable----
Line Input #1, TextLine
'---split line using "," delimeter------
TxtArray = Split(TextLine, ",")
'---Check to ensure there are at least (3) fields (Array index begins
with "0")
If (UBound(TxtArray) >= 2) Then
' --- Check to see if value already exists ----
If (Not Dict_Data.exists(TxtArray(0) & "_" & TxtArray(1))) Then
'-- Add Record ---
nRows = nRows + 1
Sheets(MasterSheetName).Cells(nRows, "A").Value = TxtArray(0)
Sheets(MasterSheetName).Cells(nRows, "B").Value = TxtArray(1)
Sheets(MasterSheetName).Cells(nRows, "C").Value = TxtArray(2)
'----- Add to Dictionary Object --------------
Dict_Data.Add TxtArray(0) & "_" & TxtArray(1), TxtArray(2)
'----------------------------------------------
Else ' Update Record
DataRow = Dict_Data.Item(TxtArray(0) & "_" & TxtArray(1))
Sheets(MasterSheetName).Cells(DataRow, "C").Value = TxtArray(2)
End If
End If
Loop
Close #1 ' Close file.
End Function
----------------------------------------------------------
----------------------------------------------------------
----------------------------------------------------------
==============================================================
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar