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

 

The reason for asking about the "Scripting.Dictionary" text value is several. What if I want to create a Dictionary Data for several workbooks? How do I distinguish one Dictionary Data of one workbook from another? Does that name qualify it somehow? Is the name {workbook}.Scripting.Dictionary? Does this Dictionary only "live" as long as the Macro runs?

--- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@...> wrote:
>
>
> ________________________________
> From: danielrose02 <dan.rose@...>
> To: ExcelVBA@yahoogroups.com
> Sent: Thu, July 12, 2012 3:39:08 PM
> Subject: [ExcelVBA] Re: Need help on a Macro to compare two spreadsheets
>
>  
> Thank you. More questions:
>
> 1. The "Master_Data" workbook actually is made up of 3 worksheets:
> 1.a Chart of Summary Pivot Table.
> 1.b Summary Pivot Table
> 1.c LTD Weekly KPI
> 2. I want to perform all of this updating on the last worksheet (LTD Weekly
> KPI). So, for the line of code:
>
> nRows =
> Application.WorksheetFunction.CountA(Sheets(MasterSheetName).Range("A1:A1000000"\))
>
>
> How do I stipulate to use that particular worksheet?
> I anticipated this need.  Therefore, I made the sheet name a variable.
> Find the line that says:
> MasterSheetName = "Master_Data"
> and change it to:
> MasterSheetName = "LTD Weekly KPI"
>
> 3. In the line of code:
>
> Set Dict_Data = CreateObject("Scripting.Dictionary")
>
> Is the text "Scripting.Dictionary" a reserved word or phrase?
> Yeeessss... It is the definition of the Dictionary Object..
> Why do you ask?
>
>
> --- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@> wrote:
> >
> > There are LOTS of ways to determine how many rows
> > are in any given worksheet.
> > Which is "best" is different for any given situation.
> > I'll try to describe how these two techniques work.
> >
> > UsedRange
> > ----------------
> > As you add data to a worksheet, this "Range" increases in size. 
> > In earlier versions of Excel, when you delete rows, this range didn't
> > decrease until you exit the worksheet and re-open it.
> > (actually, I think it reserved the rows until the "redo" option was no longer
> > available)
> >
> > However, it looks like in Excel2010, it seems to update appropriately.
> >
> > WorksheetFunction.Counta
> > ----------
> > for this technique to work, your data has to be without blanks in the column
> > you're counting.
> > If you have header rows that do not have data, then this technique must be used
> >
> > with caution.
> >
> > I think for your situation, either technique may be used.
> >
> > 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
> > -----------------------------------------
> >
> >
> >
> >
> > ________________________________
> > From: danielrose02 <dan.rose@>
> > To: ExcelVBA@yahoogroups.com
> > Sent: Thu, July 12, 2012 1:48:43 PM
> > Subject: [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]
> > >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
> [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 (10)

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

.

__,_._,___