1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Help With Coding

Discussion in 'Business Applications' started by jo15765, Nov 2, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. jo15765

    jo15765 Thread Starter

    Joined:
    Oct 11, 2011
    Messages:
    307
    I have the an 3 separate arrays, for 3 separate reports that are run daily. For example on Monday, I have report 1 (which has its own array) report 2 (which has its own array) and report 3 (which also has its own array) and the same for Tues, Wed... etc up to Friday. I want to build a form, that I can enter for an example Report_1 and enter a new workbook name "Michael.xls" and push a button and it will add "Michael.xls" to the array. Also, I want to be able to enter a workbook name (well use the same book name) "Michael.xls" and push a different button and it will remove "Michel.xls" from the array.


    I attached a sample form, with a little code in there that will hopefully help...
     

    Attached Files:

  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    I'm having a real hard time understanding exactly what you are trying to accomplish with your code. Can you explain exactly what each of the report macros is supposed to do? My first thought is that you are better off storing the array values in an unused range of cells instead of an array and have your macro loop through the cells to obtain the values to use.

    Rollin
     
  3. jo15765

    jo15765 Thread Starter

    Joined:
    Oct 11, 2011
    Messages:
    307
    The 1st Macro that is being run is a Quarterly report, and it is about 20 different reports that are run, hence the names: "Test.xls", "Roger.xls" etc etc....

    The 2nd Macro that is being run is a Monthly report and again is about 20 different reports that are run.

    The 3rd Macro is a weekly report that is being run and also is about 20 different reports that are being run.

    And then of course onto the next day is the same 3 Macro's but 20 different reports.

    The reason I am wanting to be able to have a form, where I can add/remove file names from the Array's is instead of having to open the VBA coding, manually remove the file name, I can just open this form, and do it directly from there. It will save time, as well as if a "low level" excel user is running the data they can use it much easier than having to find me!

    Does that help clear things up? If not let me know!
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Take a look at the attached sample workbook I created to give you a general idea of how to accomplish this. You need to declare the Array as Public at the very top of the module so it is available to other subroutines and then use the REDIM PRESERVE function to resize the array before adding new elements. Open my sample workbook and first run the macro called "CreateArray." Then when the form opens enter a value in the textbox and click the appropriate button to add/remove the elements to/from the array. Hope this helps.

    Rollin
     

    Attached Files:

  5. jo15765

    jo15765 Thread Starter

    Joined:
    Oct 11, 2011
    Messages:
    307
    That is exactly what I am wanting to do! I am having trouble wrapping my mind around how I would do it for numerous Arrays. For example the sample workbook that I uploaded has...Monday_1, Monday_2, Monday_3...and then my actual workbook has the same for Tues - Fri as well, so it's numerous Arrays (totaling 15) how would I show which Array (or which day of the week) I was wanting to add/remove to/from?
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Add a drop down combobox on your form and populate it with the Array names.

    Rollin
     
  7. jo15765

    jo15765 Thread Starter

    Joined:
    Oct 11, 2011
    Messages:
    307
    I added a combo box on my form, and placed code behind it to add the reports. But now when I select a report, and input the name to add/remove from the array. It fails.

    I attached the workbook, to see if maybe I just typed something wrong...
     

    Attached Files:

  8. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    First of all we need to know how your arrays are going to get initialized. Are you planning on creating and populating the arrays all at once automatically when the workbook first opens or are you going to use some other event? Also I don't see where you added any code behind the combobox. The code to add to the array should be located in the button click event.

    Rollin
     
  9. jo15765

    jo15765 Thread Starter

    Joined:
    Oct 11, 2011
    Messages:
    307
    The Array's have already been written and are populated, and are being called from the OnClick() event of a button on a seperate UserForm. I guess that is how they are initialized as well, is once the buttonclick happens the Array is INitialized.

    As far as the combo box goes, I was wanting to add/remove the specific entry in the combo box depending on which option the user suggested from the selected array.

    So behind the combo box if the user elects to add I would need to add the input from the combo box into the array, correct? So the coding would need to be modified to something like this:
    Code:
    Dim varBooks
        Dim varBook
        Dim wb As Excel.Workbook
        Dim formEntry as ComboBox1.Value
        varBooks = Array("Test.xls", "Run23.xls" & formEntry)
    
        For Each varBook In varBooks
            Set wb = Workbooks.Open(Filename:="C:\Test\" & varBook)
            With wb
                .SaveAs Filename:="C:\Test\Completed" & ".xls'"
                .Close False
            End With
        Next varBook
    
    Is that somewhat close, or am I way out of the ballpark?
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1025184

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice