PDA

View Full Version : Copy inputbox-selection of multiple files in a new spreadsheet on new rows



nanjeh
11-14-2008, 09:33 AM
Dear experts,

I've been buisy with this script for two and a half day now, and i'm about to give up. I managed to make a script for opening a folder with workbooks and preform the same recorded macro on the maps in the folder (with browsing). I'm new to VBA so i was quite content with myself (if you're interested i can post the script).
The next horde i had to take though, was getting information from those same workbooks in a new workbook. For instance, in the folder (which i want to declare by browsing; but i think i already tackled that) are 10 files with similar structure but with other data. I want to make a script where i can copy the information in cell A:A1 of file1 in the cell A:A1 of NewWorkbook and then copy cell A:A1 of file2 in cell A:B1 of NewWorkbook and so on, untill all A:A1 cells of all files are in NewWorkbook (looping). Oh, and I need to specify the cells i want to copy by an Inputbox, so that others can use the tool too, without having to adjust the script. Here's my script i made so far, with two functions (one for browsing the map and one for the inputbox for the cells to be copied) and the sub where i'm stuck in. I really hope somebody's kind enough to help me out. Thanks in advance, Nanjeh

Option Explicit

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level

Dim ShellApp As Object

'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0

'Destroy the Shell Application
Set ShellApp = Nothing

'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\\ (as in \\servername\sharename. All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select

Exit Function

Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False

End Function

Function TestInputBox() As Variant
Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox( _
"Select cell(s) or row(s)", , , , , , , 8)
End Function


Sub GetXLSData()

Dim fs, fi, fc, foc, cd, cdc
Dim wb As Workbook
Dim foldername As String
Dim CalledData As String
Dim r As Long

'Make new workbook to put data in, beginning from cell A1
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="GetData.xls" 'change if desired
ActiveSheet.Range("A1").Select

'Set object properties
foldername = BrowseForFolder 'see function mentioned above
Set fs = CreateObject("Scripting.FileSystemObject")
Set foc = fs.GetFolder(foldername)
Set fc = foc.Files
r = 1

CalledData = TestInputBox
'Set cd = CreateObject("Scripting.FileSystemObject")
'Set cdc = cd.Select(CalledData)


'Make loop for each of the containg files in foldername
'Copy the data which was selected with thefunction TestInputBox
'Select Activesheet in GetData and paste selected data in cell A1

For Each fi In fc

Select Case r

'Range(CalledData).Select
'Selection.Copy
'ActiveSheet.Range("A1").Select
'Selection.Paste

'save it
Workbooks(fi).Close
Application.ScreenUpdating = True

r = r + 1

Next

End Sub

GTO
11-17-2008, 03:46 PM
nanjeh,

Just noticed that there was no interaction on this. Were you still looking for a solution?

Mark

nanjeh
11-18-2008, 01:50 AM
still awaiting and abiding... :)
i hope it's not too much of a deal for the real VBA programmers here.
still, i'm stuck

GTO
11-18-2008, 03:25 AM
Greetings and Salutations Sir,

While I notice that you have been a member for over a year, I see that this is your first post. Please let me "re-welcome" you :-) I've just been a member for a few months, but I learned from visiting for a couple of
years. Now I don't know why in heavens I waited so long for!

Anyways, as you probably already know, there are a lot of nice folks here,
so I'm sure someone will be able to give you a great answer/solution. In fact, while I'm far from being the "sharpest knife in the drawer" here, I hope I am able to help, especially when meeting someone who actually waits a couple of days to "bump" their post.

Sorry that your question seemed to get 'missed', but I think that part of the reason was the wording. Since it is late here, you may well get help from someone else first, but hopefully I can assist you to a lesser degree by getting some clarification to your problem.

Ready? Okay:

You say you've got enough code (script) written to "open" or specify a folder that contains workbooks, and perform actions (a recorded macro) on "the maps" in the folder.

Now in your second paragraph, you mention that you want to take information from those same workbooks (which you referred to as "maps" before) and put some information from each of these workbooks, into a new workook.

So, I think we need to know, what do you mean when you say "maps"?

Are "maps" referring to workbooks; or if not, are they files that display literal maps? I do not say this with any facetiousness at all, I very much want to understand what exactly we are trying to help with.

To that end, I ask that you post (attach) a copy or an example of the workbook that you have your code in so far, as well as a copy of one of these "map" files. Of course it is important that any files/workbooks attached do not contain any sensitive company information, or personal information, or "proprietary" information.

Since you haven't posted before, I'll tell you that you can only attach one attachment to a post, so, you will want to copy both the example workbook containing the code, as well as the example "map" file into one zip folder.

After we see the examples, I'll bet you we can help :-)

I hope this helps a little,

Mark

nanjeh
11-18-2008, 08:44 AM
I'm impressed already by your effort to understand my problem. I'm Dutch, and I've made a translation error by referring to the mentioned folders as "maps", which of course is in English a type of geographical visualization. Folders are in Dutch Windows "mappen". Hence why. I've attached a file with the macro and some example files. The idea is that i need to create a new workbook (e.g. GetData.xls) and let the macro fill every row of that workbook with the data on row A:A1.. E:E1 from each workbook in the Folder (preferrably by specifying the cells and the folder containing the workbooks by browsing (i.e.; not a fixed code).
For instance; i need all the Earning per share info of every company of every year subsequently copied in a new workbook.
I'll post the other macro i've put together in a next post, where i do the opposite; copying a selection in every workbook.
Thanks a lot in advance Mark.

nanjeh
11-18-2008, 09:04 AM
Hi Mark (and others),

Here's the code of the batch input copy macro i put together by copying others and a little logic from myself.

Looking forward to your reaction.

nj

GTO
11-18-2008, 11:42 PM
Greetings nj,

I did download your workbooks. It may take me a bit to take a better look, as to me at least, it appears that you have indeed combined examples, and there are some issues.

Thanks for clearing up "maps" or mappen, now I know a word in Dutch!

Mark

nanjeh
11-19-2008, 01:39 AM
Great!
Really appreciate your help.
If I need to do something in the meantime or need to clarify things, let me know.
I think primarily, that i don't get the loop function and which loop to use, and how to put the data below each other. The function map browsing worked well in the other maco i put together (see batchinputcopy.zip), and i als got the row selection working (at least it seemed so).

Nanjeh

GTO
11-20-2008, 08:07 PM
Hello Nanjeh,

Well, you said to ask questions, so I want to make sure that we are on the "same page" so-to-speak; mostly so that suggestions do not backfire...


I managed to make a script for opening a folder with workbooks and preform the same recorded macro on the [workbooks] in the folder (with browsing).

This would be "Batch input copy.xls".

I believe I understand what is going on here (goal-wise), in that you want to perform some basic actions (inserting a column and putting the new year) against all files in a folder. So I can see why you want to run the macro from a seperate file, so that the macro can easily be changed to current needs.

Now as to then making a new file and retrieving data from various workbooks, I catch that you want to give the user some choice as to what source range to retrieve. This part would seem the challenge.

What would be the limitations in the range the user sets as the source? For example, you used cell A1 as a source, and this would be pretty easy to logically place the values in a series of cells in the new workbook.

But... what happens if the user selects a range of multiple rows and/or cells?

Mark

nanjeh
11-21-2008, 04:29 AM
Dear Mark,

It's so nice you're actually putting time in this. I'm glad we're on the same line. `I believe I understand what is going on here (goal-wise), in that you want to perform some basic actions (inserting a column and putting the new year) against all files in a folder.` Yep. That?s what i?m trying to do. As well as copying data into the batch, as extracting data from the batch.

Referring to your question regarding multiple cell/row selection. It's not a problem if it's not possible to put multiple row selections below each other. Though in my reasoning it's the same procedure, but with another selection to be copied. But a selection of a row (or at least multiple cells in a row) should be possible, right?

I got it working before with the next code:

Function TestInputBox() As Variant
Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox( _
"Select cell(s) or row(s)", , , , , , , 8)
End Function

Which I set as a function so that I could refer to it later on, in the sub.
I got stuck with the loop though. But perhaps I got it al wrong, and it?s not a correct code to use.

Thankfully,

Nanjeh

GTO
11-21-2008, 08:57 PM
Referring to your question regarding multiple cell/row selection. It's not a problem if it's not possible to put multiple row selections below each other. Though in my reasoning it's the same procedure, but with another selection to be copied. But a selection of a row (or at least multiple cells in a row) should be possible, right?

As to allowing a multiple cell selection, Yes. Please note that I wasn't stating it as impossible as to allowing multiple rows/columns; I guess I was just trying to "picture" it in my head (vacuous area that that is...).

Anyways, if I'm following your code correctly, I saw what I consider a couple of issues. While I don't like guessing "too much" at what someone is looking to do, I think that I am clear that you are wanting the user to be able to set (pick) the cell(s) or range, based on what sort of range has values in the source books that we'll be opening, right?

In other words, let's say there's stuff in the (examples) "Aegon", "ING", etc, workbooks in the range A1:C7 (like these presently do have). But let's say next week, all the workbooks being looked at are going to have stuff in the range B2:H15. My understanding is that you want the user to be able to choose whatever range they want, and the program will then take this range (a copy or copy of the data) and paste/enter these ranges, one below the other, into the new workbook.

Sorry to be asking for such clarity, but you can see where this changes the start of your procedure. As it stands now, you first create a workbook, then ask for the user to pick a range. We would need to open (or already have opened) one of the source workbooks in order that the user could accurately choose the appropriate range.

I hope that all made sense thus far.:think:


I got stuck with the loop though. But perhaps I got it al wrong, and it?s not a correct code to use.

Your loop needs some work; this is not a big deal at all. I think we should take one step at a time, as I believe this will assist you better.

Hope to help,:)

Mark

GTO
11-21-2008, 09:00 PM
Oops!

PS - my apologies, as I just realized that you signed 'nj' and I still used nanjeh later. What is your name or what is your preference for nickname. (Sorry, sometimes I get rushed/distracted/or just brain-faded...

Mark

nanjeh
11-24-2008, 02:17 AM
Hi Mark,

I can't agree more with what you stated in your last reply. That's why I left those function codes in the macro, so that you could see my intentions. Thus; cell selection, at choice of the user, without having to alter the code manually (an inputbox; as the users have even less understanding of it than me). I'm eager to see what suggestions you may have. My name is Nanjeh, but I sometimes shorten it with my initials. Pick whatever you like :)

:thumb

Nanjeh

nanjeh
11-26-2008, 01:59 AM
Hm.
I really wish somebody could help me.
In the above correspondance, I think the issue is quite clear. I probably don't get the For Each function and how to put data in each row below (r+1). It has been quite a time now since my first post. Are you still out there Mark? Somebody?

GTO
11-26-2008, 03:29 AM
Dear Nanjeh,

I most certainly haven't forgotten you. I did spend some time on it yesterday, but forgot to take my flashdrive w/me today. Now I would be the first to state that there are others here who are faster typists, better coders, etc., and they are certainly most welcome to join in. That said, as I believe I stated earlier, there are a couple of "issues" (in my opinion) with the way you had things working. Don't give up, for all you know, as soon as I complete my suggestion, this may well trigger another member to seeing a "leaps and bounds" advancement.

Anyways, do not lose heart. As most here do, I tend to stay "too busy". Further - I do forget stuff once in a while.

But - I am fairly obstinate in tackling a problem and not insincere when stating I'll do something.

Mark

nanjeh
11-26-2008, 06:18 AM
Great.
On my behalf; I don't want an easy fix, but want to see how I can do this on my own next time. I've bought the Jeff Webb book a month ago (Programming Excel with VBA and .NET) but imho it's not really clear in how to nest functions (like For Each) and the logic in building up subs. After some days of correspondence I just wanted to talk about code. I didn't doubt your sincerity though and I do already greatly appreciate your efforts thus far. I admit, I was getting a little impatient. I really appreciate your help Mark and of course will wait a while longer. Good hearing from you again.

Merci

Nanjeh

GTO
11-27-2008, 06:27 AM
On my behalf; I don't want an easy fix, but want to see how I can do this on my own next time.

Hi Nanjeh,

Well... as I've expressed on several occassions, I are a dang slow typist, not to mention, only part way there, as to being a decent coder. Thus - this and given the original code and goal, I found it best (for me) to at least produce a workable (hopefully) solution in one swoop. I am sorry about that, as when learning, I agree that getting it a "piece-at-a-time" can be beneficial, as it makes me "stretch" my understanding. Further - I was recently chided a bit (in a friendly manner) for excessive "guessing" at what the OP wanted. All that said, I hope this is on track with what you want.


After some days of correspondence I just wanted to talk about code. I didn't doubt your sincerity though and I do already greatly appreciate your efforts thus far.

Thank you for that.


I admit, I was getting a little impatient...

You have my utter and most sincere empathy. I am in my forties, yet I still have the patience of a six-year old when frustrated and wanting to 'get' (learn) something! My poor friend Scott (Nick: Demosthine) has received plenty of calls when he was no doubt busy. In fact, I have often said that my friends are not such due to my flawlessness, but rather due to their forgiveness. Shoot - even in joining this forum, I'd liked to have pestered Bob (XLD) about half to death in my first few days of membership.


Merci

Hey NJ, I now know that "blad" must be Dutch for "sheet," and "mappen" is a folder (singular or plural?). What does "Merci" mean?

Okay, hopefully I have not gone astray of what you are looking to do.

Here is the main procedure, and I have included the WB as an attachment. If any of my explanation is poor, and/or I headed in a wrong direction, yell out.

While I wasn't able to just explain the loop on its own, I left stuff like screen updating undone, so that you still can fine-tune.

I truly hope this is of help,

Mark

Sub GetXLSData_Guess()
'// Declare File System Object related as variants. //
Dim fs, foc, fc, fi

'// Variables that were already in your example. I left these named as is; hence, the //
'// inconsistency between naming styles... //
Dim wb As Workbook
Dim foldername As String
Dim CalledData As String

'// Declare all other variables as their respective types. //
Dim wbNewBook As Workbook
Dim wbPatternWB As Workbook
Dim wksDestination As Worksheet
Dim strThisWBPath As String
Dim strNewWB_PathOrFNam As String
Dim strSourceRange As String
Dim rngUserSelected As Range
Dim rngSource As Range
Dim rngDestination As Range
Dim lngSheetIndex As Long
Dim lngHgt As Long
Dim lngWid As Long
Dim lngRow_Upper As Long
Dim lngCol_Left As Long

'// Rather than adding a workbook and depending upon its 'Active' status, we can //
'// be more explicit in setting the added workbook as an object. //
Set wbNewBook = Workbooks.Add(xlWBATWorksheet)

'// I don't know why, but at least on my POL (Poor Old Laptop) in XL2000, it seems //
'// that simply assigning straight to ChDir has spotty results. So, we'll //
'// initialize a string, then we'll change the directory to where this workbook is //
'// located. Change to suit. //
strThisWBPath = ThisWorkbook.Path & Application.PathSeparator
ChDir strThisWBPath

'// Now that we've changed directories (actually a sub-directory, or more lately //
'// referred to as a Folder... we can use GetSaveAsFilename (see VBA Help) to return//
'// the path and name of the new workbook (WB) you want to create. //
strNewWB_PathOrFNam = Application.GetSaveAsFilename( _
InitialFileName:=strThisWBPath & "My_Combined_Data", _
FileFilter:="Microsoft Office Excel Workbook(*.xls), *.xls", _
Title:="Choose a name for the new Workbook")

'// (VBA Help) In case the user cancels, we'll destroy the newly created WB, and //
'// exit the Sub. //
If strNewWB_PathOrFNam = "False" Then
wbNewBook.Close SaveChanges:=False
Exit Sub
Else
'// Else - the user used the suggested name, or entered a new one, and we'll now//
'// save the new WB. Note that as you may do this daily, I turned Alerts off, //
'// so that an old WB is simply overwritten. Change to suit. //
Application.DisplayAlerts = False
wbNewBook.SaveAs Filename:=strNewWB_PathOrFNam, _
FileFormat:=xlNormal, _
AddToMru:=False
Application.DisplayAlerts = True
End If

'// (See VBA Help) Left, Len, and InStrRev are used to coerce a returned string of //
'// the path to the new WB (ie - discluding the WB's name). //
strNewWB_PathOrFNam = Left(strNewWB_PathOrFNam, _
Len(strNewWB_PathOrFNam) _
- (Len(strNewWB_PathOrFNam) _
- InStrRev(strNewWB_PathOrFNam, "\", -1, _
vbTextCompare) + 1))

'// Definitely change to suit. I included using the optional arguments (arg or //
'// args) to set where the new Shell opens to (that is, what sub folder is 'drilled //
'// down' to), as well as setting the text to help the user know what to do. //
foldername = BrowseForFolder(OpenAt:=strNewWB_PathOrFNam & "\", _
TitleBarText:="Select the folder that has ONLY has" & _
" files (workbooks) that can be opened" & _
" and data extracted from.")

'// In case no folder was chosen... Close the new WB and Exit. //
If foldername = "False" Then
wbNewBook.Close SaveChanges:=False
MsgBox "A valid folder must be selected. Operation cancelled.", vbOKOnly, ""
Exit Sub
End If

'// If the user selected a folder, we can continue, so Change Directory to where //
'// the files are that we'll be getting data from. //
ChDir foldername & "\"

'// Again - as I know of no other way to set what folder or sub-directory that //
'// GetOpenFilename will initially display, we used ChDir to aim it in the right //
'// direction. Now, we'll have GetOpenFilename display the files in the Folder that//
'// we picked with the Browser. As mentioned, this needs done before rotating //
'// through the various WB's, as we need to give the user a chance to pick the //
'// Range he wants to extract data from. (See VBA Help for GetOpenFilename) //
strNewWB_PathOrFNam = Application.GetOpenFilename( _
FileFilter:="Microsoft Office Excel Workbook(*.xls), *.xls", _
Title:="Pick a file to Set the copied range pattern", _
MultiSelect:=False)

'// A wee bit repetitive, but too tired to write a seperate/tiny function... //
If strNewWB_PathOrFNam = "False" Then
wbNewBook.Close SaveChanges:=False
Exit Sub
End If

'// We're still going, so the user must have selected a WB to open. We'll open it //
'// ReadOnly, so user can't goober it up... //
Set wbPatternWB = Workbooks.Open(Filename:=strNewWB_PathOrFNam, _
ReadOnly:=True, _
AddToMru:=False)

'// We could probably use some error handling elsewhere, but since my POL didn't //
'// whine nary once, we'll just set error handling here; wherein, we hastely leave //
'// the sub if the user doesn't pick a Range. No doubt room for improvement here. //
On Error GoTo errBail
Set rngUserSelected = Application.InputBox( _
Prompt:="Select the range you wish to retrieve", _
Title:="", Type:=8)
On Error GoTo 0

'// Now, while we still have the WB opened that we are patterning out search from, //
'// let's snatch some info. //

'// ...like, which sheet of the source WB's we're getting stuff from. //

'// NOTE: leastwise in this example, the same sheet in all the source WB's must //
'// contain the data sought. //
lngSheetIndex = rngUserSelected.Worksheet.Index

'// ...and... how 'tall' and 'wide' the range is that the user selected, as well as //
'// the address of the range. //
lngHgt = rngUserSelected.Rows.Count - 1
lngWid = rngUserSelected.Columns.Count
strSourceRange = rngUserSelected.Address

'// We're done collecting info reference the user's selected Range, so we can close //
'// the WB. //
wbPatternWB.Close SaveChanges:=False

'// Initialize these at one (1), so that cell A1 in the new destination WB is where //
'// we start "copying" data to. //
lngRow_Upper = 1
lngCol_Left = 1

'// Now, as your example had, we'll get the Folder and Files that we'll be extracting//
'// the data from. //
Set fs = CreateObject("Scripting.FileSystemObject")
Set foc = fs.GetFolder(foldername)
Set fc = foc.Files
'// For Each File (WB) in the Folder selected... //
For Each fi In fc
'// ...Open the file and set it as a WB... //
Set wb = Application.Workbooks.Open(Filename:=foldername & "\" & fi.Name, _
ReadOnly:=True, _
AddToMru:=False)

'// Set the source range to the user's previous instruction in the opened WB. //
Set rngSource = wb.Worksheets(lngSheetIndex).Range(strSourceRange)

'// Set the destination worksheet to the only sheet in the WB we created... //
Set wksDestination = wbNewBook.Sheets(1)
'// ...then Set the destination Range equal to the user's instruction. //
Set rngDestination = _
wksDestination.Range(wksDestination.Cells(lngRow_Upper, 1), _
wksDestination.Cells(lngRow_Upper + lngHgt, lngWid))

'// Now, since we have created two equally sized ranges, rather than maybe //
'// copying whatever hideous patterns or borders the users of the 'child' WB's, //
'// we'll just snatch the data as an array of values. //
rngDestination.Value = rngSource.Value

'// Reset what row(s) in the destination WB will get the data from the next WB. //
lngRow_Upper = lngRow_Upper + lngHgt + 2

'// Close this round's source WB. //
wb.Close SaveChanges:=False

'// Loop until done. (Hey, I think we finally got to your question as to looping //
'// through them. A wee bit slow, ain't I? //
Next

'// After all done (Change to suit), we can name the sheet in the destination WB //
'// and AutoFit the columns (VBA Help). //
With wksDestination
.Name = "Nanjehs compiled data"
.Range(.Cells(1, 1), .Cells(1, lngWid)).EntireColumn.AutoFit
End With

'// Finally - we'll resave the newly created WB after we stuck all the data in it. //
wbNewBook.Save

'// Optional: Close this workbook, as we're all done. //
ThisWorkbook.Close SaveChanges:=False
'// If we didn't have any errrorrrs at line 185 (or thereabouts), we need to Exit the //
'// sub here. This works as a substitute 'End Sub' when including an error handling //
'// routine. //
Exit Sub
errBail:
MsgBox "An error in selecting a range has occurred.", 0, ""
'// This error handling really just bails, and probably just left the created WB //
'// resident. Change to suit. //
End Sub



brevis esse laboro, obscuris fio

nanjeh
11-27-2008, 07:53 AM
pff... how could i ever come to that by myself?
just (tried to) read it through for the first time...
And tested the code a couple of times and it is exactly what i was looking for
wow
And i saw it was a version 4...
Just curious, is this an advanced get-out-of-hand hobby of yours or are you professionally related to VBA?

nanjeh
11-27-2008, 07:58 AM
Mappen = plural
Merci = Frech = Thanks
Ontzettend veel dank = Dutch = bountiful thanks

nanjeh
11-27-2008, 07:59 AM
First. The loop. Why is the -1 in
'// ...and... how 'tall' and 'wide' the range is that the user selected, as well as //
'// the address of the range. //
lngHgt = rngUserSelected.Rows.Count - 1
lngWid = rngUserSelected.Columns.Count
strSourceRange = rngUserSelected.Address ?

nanjeh
11-27-2008, 08:04 AM
I would have made a simple copy paste. Instead, you made a reference to the cells and changed the reference properties (to value). Nice.
I get the

lngRow_Upper = lngRow_Upper + lngHgt + 2 but I don't understand how the loop know where to end. Or is it inherent in a For Each loop?

nanjeh
11-27-2008, 08:10 AM
Your path defining is way above my head and shows your advanced skills. (e.g.:
Set wbNewBook = Workbooks.Add(xlWBATWorksheet)
and
strThisWBPath = ThisWorkbook.Path & Application.PathSeparator
or
lefts and lens.
I think i can follow your building up, but i would never come to that by myself

nanjeh
11-27-2008, 08:19 AM
So it is clear. I have a lot to learn. Still VBA is now just a means to me but shifts to a reasoning I want to comprehend. I've adjusted a few name settings and +1 row and all is done. I think I would have be even as satisfied (though in ignorance) if I just got the For Each loop right (which indeed is not much of a deal), but all this time and effort you put in, made this code a solid and bug free program. Thanks Mark. I made a contribution to this site as a Thanksgiving Day expression.

GTO
11-27-2008, 09:03 AM
pff... how could i ever come to that by myself?

You will. Like any mechanics, takes studying and/or blowing up a few things along the way (thankfully, PCs are awfully forgiving).


just (tried to) read it through for the first time...

Well, hopefully my commenting was halfway decent, but it has been a long day. I'll check later, and if you have any questions that have not been answered, I'll do my best to explain :-)


And tested the code a couple of times and it is exactly what i was looking for
wow

You are very kind for the "Wow", the code (partially intentionally and partially yours truly) still needs work. I am very glad that I was on the same page. I think after studying it a bit, my prior verbiage as to what needs to happen in order will make sense.


And i saw it was a version 4...

...strictly a habit of mine. Back at the blowing stuff up part... I've had a few Excel workbooks go corrupt + aforementioned slow typist = make copies or saveas during development keeps Mark from punching monitor...


Just curious, is this an advanced get-out-of-hand hobby of yours or are you professionally related to VBA?

Neither. Ever heard the expression "pay (or play) it forward"? My initial thoughts in joining the forum were that maybe I could "pay back" a portion of the knowledge that I have received in reading and studying the work of others here, by maybe answering simpler questions. Quite frankly though, it seems a quite nice thing to discover that "stretching" my limited knowledge by sometimes taking on a "bigger" question (least for me) helps me as much as the OP.

That said, while certainly not any part of my job descript, my interests in coding were born, and still are, primarily related to decreasing mindless and repetitious tasks/data entry.

Well, off to bed for this lad. Again, any questions, feel free to ask. Though I'll misquote a bit, the forum's goal is to "bring vba to the world", and I certainly like helping with whatever little contribution I may have to offer (time, family, etc, permitting).

@All, leastwise those of us who celebrate this holiday:

Happy Thanksgiving! One of the things I am thankful for is the kind-hearted and patient help I have received here.

Mark

GTO
11-27-2008, 09:24 AM
OOPS! I didn't see the rest of your posts before responding. CHeck later, I'll get back to this thread.

Mark

GTO
11-27-2008, 09:16 PM
Greetings Nanjeh,

Hopefully I'll be able to give answers that will make sense and assist you. Ready? Here we go!


First. The loop. Why is the -1 in
'// ...and... how 'tall' and 'wide' the range is that the user selected, as well as //
'// the address of the range. //
lngHgt = rngUserSelected.Rows.Count - 1
lngWid = rngUserSelected.Columns.Count
strSourceRange = rngUserSelected.Address?

lngHgt is reduced by one (1), to adjust for the addition of the starting row number (that is, the uppermost row or top row) where we are currently placing the source data range. Ack! I can already tell I'm not doing well verbiage-wise; let me explain it this way:

Set rngDestination = _
wksDestination.Range(wksDestination.Cells(lngRow_Upper, 1), _
wksDestination.Cells( lngRow_Upper + lngHgt, lngWid))

Now in the above, we defined the range by using Cells(row, col) twice, in order to set the start of the range and the end of the range. Now let's say lngRow_Upper (the current uppermost row) was 1. Let's also assume that the user had selected "A1:C5" from the source workbook as the range to snatch. Now since this means lngHgt = 5, and lngRow_Upper was 1, If we didn't reduce lngHgt by 1, we would have ended up with "A1:C6" as our destination range, which is one row too many, and will fill the bottom most row with errors...


I would have made a simple copy paste. Instead, you made a reference to the cells and changed the reference properties (to value). Nice.
I get the
lngRow_Upper = lngRow_Upper + lngHgt + 2
but I don't understand how the loop know where to end. Or is it inherent in a For Each loop?

Okay, sorry - but I'm not sure if you're asking as to where the loop "end" meaning the bottom of the loop, or whether you mean "end" as in finished looping... so hopefully this answer will cover both sensibly.

The Next is where the loop cycles so-to-speak. As to being all through or finished, a For Each runs until it has "rotated" thru all the items in the collection. For a quick example, take a new blank workbook and insert this code. To do so, right-click on Blad1's tab, and select View Code, then just paste the code.

Private Sub Worksheet_Activate()
Dim rngTestRange As Range
Dim rngCell As Range

Set rngTestRange = ActiveSheet.Range("A1:A5")

For Each rngCell In rngTestRange

MsgBox "The value of Cell: " & rngCell.Address(False, False) & " is: " & rngCell.Value

Next rngCell

End Sub

Now on Blad1, enter 1, 2, 3, 4, 5 (or whatever values you want) in cells A1, A2, A3, A4 and A5.

Then, select another sheet, and then re-select Blad1. Since this code runs when the sheet is activated, you'll see how it checks each item (ea cell in case of a range of cells), and returns ea cell's address and current value. Does that make better sense now?


Your path defining is way above my head and shows your advanced skills. (e.g.:
Set wbNewBook = Workbooks.Add(xlWBATWorksheet)
and
strThisWBPath = ThisWorkbook.Path & Application.PathSeparator
or
lefts and lens.
I think i can follow your building up, but i would never come to that by myself

As to the Set Statement, look for the help topic "Set Statement" in the VBA Help. The folks at MS certainly give a better description than I could, but in short, this sets a reference to an Object. Thus, after the running code executes Set wbNewBook = Workbooks.Add, we can later just type (for instance) "wbNewBook.Close False" to close the created workbook w/o saving. This is because Excel recognizes wbNewBook after we Set a reference to it. In contrast, 'strThisWBPath = ThisWorkbook.Path & Application.PathSeperator' simply assigns a string to the variable. For instance, if the workbook that the code is in has a FullName of "C:\MyFolder\MySubFolder\Book1.xls" then strThisWBPath equals ""C:\MyFolder\MySubFolder\"

Hope this helped, and you have a terific day or evening as the case may be, :friends:

Mark

nanjeh
11-28-2008, 06:36 AM
Thanks for your extensive explanation. There's al lot of really new code to me here, and I willl experiment some with it. I always thought there had to be a formula given with a for each (confused with for next probably), mainly because we did our macro work in lotus 123 before (yup; oldschool). I like the pay it forward concept and hope to contribute here another time another way.

:friends:
Thanks Mark

Nanjeh