+ Reply to Thread
Results 1 to 26 of 26

Strategy Sought! How to Store data from file A in Table/Array so it can be used in File B!

  1. #1
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Strategy Sought! How to Store data from file A in Table/Array so it can be used in File B!

    Hi,

    I'm just starting a new project and thought that asking for advice on an approach at the beginning might well save me a headache later on when I hit a brick wall and am told, "you could have done it this way much easier!".

    So, the task is this...

    Every time we fulfill an order, We produce a label for every pallet that is sent out the door for that order. The label is a set template, created in Excel, and has around 12 fields which must be filled in for every pallet. At the moment this is done manually, which is both time consuming and has potential for human error!

    An order sheet will come in, which will have a list from pallet 01 to pallet xx. Each pallet will have the 12 fields filled out.


    PHP Code: 
    Pallet   Product       Field1       Field2   Field3    Field4      .... Field12
    1         Staples        01012        10        480         01     
    .....  5
    2         Glue           01022        50         50         05     
    .....  6
    3         Pencils        01024        25        200         10     
    .....  3
    4         Ruler          01026       100         50         01     
    .....  1
    5         Set square     01031         1        100         05     
    .....  0
    .             .
    .             .
    30        Pens           01112        10        200         01    .....   


    For every pallet, I need to create a new label from the master, and populate it with the info from the list.

    On completion, I will have an excel workbook with multiple sheets - in the case of the example above, there will be 30, which will be ready to be printed off as labels

    In my mind, I envisage taking this input file, saving it to memory as an array or table (note mix of digits and characters), and then being able to close the input file (rather than hopping back and forward between that and the output file). Previously, I would have created a macro to copy and paste it into the output file and then hop back and forward from the worksheet to the relevant label, copying and pasting. This just seems inefficient! I really want to do this once and absorb all data as a clean exercise!
    Once data has been stored, I wish to complete the first label (for the first pallet) using row 1 from the table.
    It will then simply be a case of incrementing down a row in the stored table, and using all the information in row 2 to create the label for pallet 2.
    Then continue this until all 30 labels have been created.

    The bit I am struggling with is realising the table creation, from there I understand how to progress! - I don't know if
    a) This is something that is easily done in Excel
    b) This is the best strategy.

    Would really appreciate input on this!




    Here's that table again in case it didn't come out correctly above...

    Pallet Product Field1 Field2 Field3 Field4 .... Field12
    1 Staples 01012 10 480 01 ..... 5
    2 Glue 01022 50 50 05 ..... 6
    3 Pencils 01024 25 200 10 ..... 3
    4 Ruler 01026 100 50 01 ..... 1
    5 Set square 01031 1 100 05 ..... 0
    . .
    . .
    30 Pens 01112 10 200 01 ..... 5
    Last edited by vba_madness; 01-11-2013 at 07:31 AM. Reason: Clarified Problem in Title

  2. #2
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Strategy Sought! Using data from file A to populate templates in File B!

    Personally

    I would:
    - create a MASTER workbook from where the entire process is controlled;
    - in the MASTER workbook I would have one console type of worksheet where you can specify parameters, click on buttons etc;
    - in the MASTER workbook I would have one worksheet (possibly hidden from regular users) that contains the formatted TEMPLATE worksheet;
    - in the MASTER workbook I would have a VBA routine triggered by a button that:
    a. prompts the user to select the order wroksheet;
    b. create an empty workbook to contain the labels;
    c. create a worksheet per pallet (based on the template) in the new workbook with all fields populated;
    d. print each individual label;
    e. prompt user to save the pallet/label workbook in case of reprints (or do this automatically)
    If you like my contribution click the star icon!

  3. #3
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    Hi OllieB,

    Thank you - this certainly seems like a sound strategy.

    To clarify, the main assistance I am looking for - is really HOW to capture the data from the input sheet (example given on my first post) and use this efficiently for each label.

    I know how to do this in a slow and repetitive way - i.e. copy to Master doc, then take value for each field and apply to each label, but that requires returning to the input for each label. I am looking to improve efficiency by storing it in a table in memory (if this is possible). I like to improve my efficiency every time I do a project, which should make the next project easier and more efficient!

  4. #4
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    What is the format of the order sheet, Excel, Word?

  5. #5
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    It can be manipulated to suit.

    So it will be in Excel, laid out as shown (unless there is a more efficient method of displaying it.)

  6. #6
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    If you post an example of the order worksheet (fictitious data is OK) and an example of the TEMPLATE used for labels I will write the basic solution for you.

  7. #7
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    Right,

    I have now taken a snap shot of the INPUT FILE; MASTER Label and PALLET01.

    Hopefully this will make a bit more sense.

    As I said, my hope was that I could capture the INPUT file in excel as a table in memory so that I could easily create the new sheets Pallet01; Pallet01 etc, and populate simply by pointing the fields to rows 01 to 30.

    Anyway, hope this now makes my outcomes clear!

    (n.b. just ignore the barcode - this is generated already from the various data fields.

    Input File.pngMaster.pngPALLET01.png

  8. #8
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    Hi vba_madness,

    I think I understand your requirement (makes perfect sense), and I was offering to write the solution for you, however I would need a copy of an order workbook and a copy of the label template worksheet. You are more than welcome to write the solution yourself.

  9. #9
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    Hi OllieB,

    Really all I was looking for was a strategy for capturing and using the input.

    Perhaps you feel writing the solution is the best way of achieving this? In which case, this would be much appreciated!

    I have included the representative files for your perusal!
    Attached Files Attached Files

  10. #10
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    Hi vba_madness,

    I was not going to hand you the solution on a silver platter, but at least point you in a possible direction.

    I have create a MASTER workbook holding various worksheets, pretty much like I described in my proposed strategy. I have also created a dummy order workbook to process. The MASTER workbook contains three buttons and macros that perform the obvious steps. I have copied the label master already into the solution for as fas as I can figure things out.

    have a look at the enclosed workbook. It can import the sample order book that you have provided, and with a click on the button, create labels that are nearly complete. You will need to check and amend some stuff, but in general it is there
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    Hi OllieB,

    Yes, I was hoping that you would Guide rather than spoon feed - it's the best way of learning!

    Many Thanks - I shall follow through your macro, learn from it and report back comments and questions on anything I don't understand!

    It will most likely be Monday before I respond as the weekend is nearly upon us!

    You have a good one and thank you!

  12. #12
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    You have a good weekend too. Note that you may find it a bit too much spoon feeding already, but you can also delete a bit of code and try to write it yourself

  13. #13
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    Hi vba_madness, did you get a chance to look at the basic solution?

  14. #14
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    Hi OllieB,

    I had a quick (10 min) play on Friday, and it seems to do what I'm looking for!
    Unfortunately Monday was assigned to a different task, but I will dedicate after lunch to attempting to understand your code.

    I find it somewhat off putting that you were able to produce that in a few hours (no doubt with other distractions), and it would have taken me a week!!

    I'll let you know how I get on comprehending your code - I see there's lots of annotation so that should make it a lot easier!

  15. #15
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    vba_madness,

    Please note that I was not able to complete all mapping for the labels (I think missing are pallet size or number of pallets or something). I have been playing around with VBA for at least 10 years, if not longer. So it is perhaps not really fair to make a comparison like that. I hope you will be able to follow my approach and the code written. If not, please feel free to post your question and I will try to answer it to the best of my ability.

  16. #16
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil


    Hi Ollie B,

    It's getting to Line 141, Public Sub pub_sub_OpenOrder()

    Please Login or Register  to view this content.

    Getting run-time error '9': "subscript out of range".

    Your comment is, "check whether the selected workbook is in the required format"....
    I don't really understand how this line is doing this, and what the required format is specified as... (in this case I'm trying to open an .xlsls file, but equally it could be a .csv etc.)

  17. #17
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    Yes and No.

    I thought we had "agreed" that the order information would be delivered as an Excel Workbook so hence importing CSV (via the current logic) is not possible. On the PARAMETERS worksheet I had specified the name of the sheet in the order workbook holding the order information, in your version "Sheet1". So if it does not find Sheet1 in the wrokbook you selected, it assumes that you may have selected an incorrect workbook.

    So now the question is:
    - where you trying to open anything else than an Excel workbook?

    If yes,
    the current version of the solution does not support this

    If no,
    does the workbook contain a sheet named Sheet1?

    if yes,
    then there is a different proble, let me know

    if no,
    then that is the reason for the error
    Last edited by OllieB; 01-15-2013 at 10:25 AM. Reason: further explanation

  18. #18
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    Ah,

    the sheet1 name had been modified to sheet1(2), which is why it wasn't finding it!

    Importing fine - Sorry! I shall continue!

    Please ignore .csv question! I was just wondering from the line of code what type of extension is it looking for (as no specific extension appears to be listed i.e. xls; .xlsls; xlma etc.

  19. #19
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    Breaking the statement down into components (for clarification purposes)

    there are two parts:
    Please Login or Register  to view this content.
    #part 1
    retrieves the value of a named range ("prmOrderWorksheet") from a worksheet referred to by the pvt_xls_Parameters variable. In the copy you have the value returned would be 'Sheet1' and you can find it on the PARAMETERS worksheet. If you select the cell (containing Sheet1) you can see the name assigned to the cell on the left side of the formula bar.

    #part 2
    this statement tries to assign the "Sheet1" worksheet object from the opened order workbook to the variable 'pvt_xls_ORDER' so it can be easily referred to during later steps. This way I do not need to refer to ActiveWorkbook or ActiveWorksheet and run the risk that the focus changes, but I can refer directly to the order workbook via 'pvt_wbk_ORDER' and to the order worksheet 'pvt_xls_ORDER' without selecting or activating them.

    Hope it makes some sense.

  20. #20
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    OllieB,

    Firstly, yes I think I understand!

    Secondly, having had a number of runs through the code, This is EXACTLY what I wanted - Thank you!

    The good/bad bit is that there are lots of bits of code in there that don't make any sense to me at the moment, which means I have a lot to learn :P

    I will continue to play around first and then I will hopefully have less questions to ask!

  21. #21
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    You are welcome. Please do not hesitate to ask for clarifications

  22. #22
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    Hi OllieB,

    I've been picking up a lot of very useful lessons from your code! :D

    I have a question....

    I am trying to understand the process when the data file(s) are opened. Specifically the piece of code below...


    Please Login or Register  to view this content.

    For another macro I am trying to write, I am wanting the user to be able open multiple files at once from the same folder (so I've changed '.AllowMultiSelect to "True").

    So, my questions are....
    Which part of this code physically executes the operation to open all the files?
    What does '.Show = -1' mean?
    Is there an easy way to store all of the file names from the opening process? (All of the file names appear in the user input bar in the 'File open window'

  23. #23
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    Hi vba_madness,

    the opening/processing of the selected files happens later in the code, this part of the code is just about showing the Open dialog to the user, and allowing the user to select multiple files and clicking on the OK or Cancel button.

    Please Login or Register  to view this content.
    check that the user actually clicked on OK/Select (and not cancel)

    Please Login or Register  to view this content.
    all selected files names are actually stored in the SelectedItems() array and can be retrieved/displayed using the above code

  24. #24
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    Ah, that makes a lot more sense!

    Sorry to ask more questions, but I take it where you have written,
    Please Login or Register  to view this content.
    I actually require to write in full, this?
    Please Login or Register  to view this content.
    ?

    So am I correct in assuming that I can store these to a string like FilenamesF(i), and then use this to open the files individually as and when I wish using Application.Workbooks.Open (FilenamesF(i)) ?

    Thank you again!

  25. #25
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    Two questions.

    Basically you have objects (a workbook, a worksheet, a range etc) and methods (actions) and information (properties) that relate to the object.

    I can write the following code (note the actual actions are not relevant)

    Please Login or Register  to view this content.
    this code works without a problem but would require the code interpretor to resolve for every line the value of ActiveSheet and set a pointer to Range("A1"). This takes a certain amount of time. What we can also do is write the code like below

    Please Login or Register  to view this content.
    Thanks to the With ... End With, the interpretor only has to resolve ActiveSheet.Range("A1") only one and it nows that all the methods and properties between the With and End With relate to that object. This is slightly faster. The name".SelectedItems(lngCount)" is therefore only a valid notation in a With ...End With construct

    In short, you can refer to objects, properties and methods using a full qualified path or use the With .. End With

    Question 2: yes you are!

  26. #26
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Strategy Sought! How to Store data from file A in Table/Array so it can be used in Fil

    Another piece of the VBA puzzle solved - Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1