[ExcelVBA] Re: Need help on a Macro to compare two spreadsheets

 

Paul,

Thank you for your quick reply. I am looking over what you sent and be doing some learning (and experimenting) with this material. So, it might be a little while before I ask more questions. There is one item I do have a question about:

I use this line of code to determine how many rows of data present:

totalrows = ActiveSheet.UsedRange.Rows.Count

However, in your example you have:

nRows = Application.WorksheetFunction.CountA(Sheets(MasterSheetName).Range("A1:A1000000"\))

What is the difference and which is better to use?

--- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@...> wrote:
>
> 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]
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (6)

Recent Activity:

• New Members 3

Visit Your Group

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

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___