Hi, I'm going around in circles and can't seem to find an approach that lets me accomplish what I need to do. Any help very much appreciated! I have a folder full of customer purchase history CSV files (about 1500). I need to import these into the new accounts package and so need to copy the data from the original CSV to a new one with all the original columns of data mapped to new locations on the new master spreadsheet. At the moment I have written two very basic macros. I have to open the file, run macro 1, close the file and move it to trash, then go to Book2 (the new master spreadsheet) and run the second macro before opening the next file and repeating the procedure. Trouble is having to do it manually and also it overwrites the data from the last file with my current novice scripts.
![]()
I'm looking for a way to open each file in the folder, run the two macros and then move the completed file to the trash before moving onto the next file in the folder. I would also like the data from the second file to be entered on the first free line below the previous file's data. I am fully aware that my current macros are not up to scratch, I thought I was a better swimmer but I now find myself on a deadline and floating in deep water!
It's worth noting that I'm working on Mac. Many thanks in advance. As you can see its from 2010. I don't remember exactly what 12 months worth of reports it was working on.
I didn't see a limiter to how many files it would open while spinning thru the folder. Just point it at a test folder with a couple files in and see what happens. You should be able to manipulate how many and which lines it outputs. I don't think your code above (using excel references) will fly on a csv. EDIT-Just noted you are running on a MAC.
Doubt if the vbscript will run as is, perhaps you can use the structure as a guide.
I believe the default is option-F8 to open the macro window. You can set it to F5 if you would like via Tools->Customize Keyboard menu. Note that F5 is currently set to the 'Go To' dialog box. Why Use Excel Macros? Learning how to automate Excel is one of the easiest ways to speed up your work–especially because Excel is used in so many work processes. Now, use the shortcut Alt + F11 (for Mac: Fn + Shift + F11) to open the VBA Editor, and double-click “Module 1” in the Project Explorer.
Example (as VBA Function) The FOR.NEXT statement can only be used in VBA code in Microsoft Excel. Let's look at how to create a FOR loop in Microsoft Excel, starting with a single loop, double loop, and triple loop, and then exploring how to change the value used to increment the counter each pass through the loop. Single Loop The simplest implementation of the FOR loop is to use the FOR.NEXT statement to create a single loop. This will allow you to repeat VBA code a fixed number of times. For example: Sub SingleLoopExample Dim LCounter As Integer For LCounter = 1 To 5 MsgBox (LCounter) Next LCounter End Sub In this example, the FOR loop is controlled by the LCounter variable. It would loop 5 times, starting at 1 and ending at 5.
![]()
Each time within the loop, it would display a message box with the value of the LCounter variable. This code would display 5 message boxes with the following values: 1, 2, 3, 4, and 5. Single Loop - Changing Increment By default, the FOR loop will increment its loop counter by 1, but this can be customized. You can use STEP increment to change the value used to increment the counter. The FOR loop can be increment can be either positive or negative values. Positive Increment Let's first look at an example of how to increment the counter of a FOR loop by a positive value. For example: Sub IncrementPositiveExample Dim LCounter As Integer For LCounter = 1 To 9 Step 2 MsgBox LCounter Next LCounter End Sub In this example, we've used Step 2 in the FOR loop to change the increment to 2.
What this means is that the FOR loop would start at 1, increment by 2, and end at 9. The code would display 5 message boxes with the following values: 1, 3, 5, 7, and 9. Negative Increment Now, let's look at how to increment the counter of a FOR loop by a negative value. For example: Sub IncrementNegativeExample Dim LCounter As Integer For LCounter = 50 To 30 Step -5 MsgBox LCounter Next LCounter End Sub When you increment by a negative value, you need the starting number to be the higher value and the ending number to be the lower value, since the FOR loop will be counting down. So in this example, the FOR loop will start at 50, increment by -5, and end at 30.
The code would display 5 message boxes with the following values: 50, 45, 40, 35, and 30. Double Loop Next, let's look at an example of how to create a double FOR loop in Microsoft Excel. For example: Sub DoubleLoopExample Dim LCounter1, LCounter2 As Integer For LCounter1 = 1 To 4 For LCounter2 = 8 To 9 MsgBox LCounter1 & '-' & LCounter2 Next LCounter2 Next LCounter1 End Sub Here we have 2 FOR loops. The outer FOR loop is controlled by the LCounter1 variable.
The inner FOR loop is controlled by the LCounter2 variable. In this example, the outer FOR loop would loop 4 times (starting at 1 and ending at 4) and the inner FOR loop would loop 2 times (starting at 8 and ending at 9). Within the inner loop, the code would display a message box each time with the value of the LCounter1- LCounter2. So in this example, 8 message boxes would be displayed with the following values: 1-8, 1-9, 2-8, 2-9, 3-8, 3-9, 4-8, and 4-9. Triple Loop Next, let's look at an example of how to create a triple FOR loop in Microsoft Excel. For example: Sub TripleLoopExample Dim LCounter1, LCounter2, LCounter3 As Integer For LCounter1 = 1 To 2 For LCounter2 = 5 To 6 For LCounter3 = 7 To 8 MsgBox LCounter1 & '-' & LCounter2 & '-' & LCounter3 Next LCounter3 Next LCounter2 Next LCounter1 End Sub Here we have 3 FOR loops. The outer-most FOR loop is controlled by the LCounter1 variable.
The next FOR loop is controlled by the LCounter2 variable. The inner-most FOR loop is controlled by the LCounter3 variable. In this example, the outer-most FOR loop would loop 2 times (starting at 1 and ending at 2), the next FOR loop would loop 2 times (starting at 5 and ending at 6), and the inner-most FOR loop would loop 2 times (starting at 7 and ending at 8). Within the inner-most loop, the code would display a message box each time with the value of the LCounter1- LCounter2- LCounter3. This code would display 8 message boxes with the following values: 1-5-7, 1-5-8, 1-6-7, 1-6-8, 2-5-7, 2-5-8, 2-6-7, and 2-6-8.
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |