• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

Alter this macro to work universally for future reports?

The attached (file initial) solution is the solution to a previous question (must see link below for background info).

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28502636.html

The expert working on my previous stage has now got to a stage whereby the resulting work sheet displays the desired modification for the source data (2.txt) that was provided.

As I have explained in the previous question. I am looking for the macro to work universally on all of my future stock reports. The reports will always be in the same format and layout but the data will change e.g. monthly data, free stock and new products (codes) added. I have attached 3 separate and different stock reports (named; 1,2,3). If the macro works successfully on all of these 3 reports we know we must be close to a solution. A good way to test a successful result is if the overall totals at the bottom of the original source data files match the totals on the parsed (modified) sheet. Free stock total MUST also match

Once you have done sufficient enough testing to ensure the macro works universally I would like a button called 'parse and modify stock report' to be added to my master utility sheet (attached). When clicked all I would have to do is select the relevant stock txt. file and click ok. The macro will then run automatically.

Any  questions just ask.
-initial-solution-.xls
1.txt
2.txt
3.txt
EE-SalesReportingUtility--2-.xlsm
0
robmarr700
Asked:
robmarr700
  • 12
  • 8
1 Solution
 
aikimarkCommented:
Please test the code with these three files.  We are here to help you, not to do your work for you.

The code has a constant string that points to the file to be parsed.  Change the string to point to each of your txt files (one at a time) and execute the routine (F5)
0
 
robmarr700Author Commented:
As I pointed out in my previous question I do not know how to do this. I have no experience of coding. This is why asked for a button to do this automatically.

I have no problems doing the so called "grunt" work as you call it but need some level of support with the  instructions and terminology in order to get me there.
0
 
aikimarkCommented:
It's about time to start learning.

1. open the workbook
2. press F11
3. find the name of the constant, "cFile"
Ctrl+F
cFile
Enter

4. Change the string literal to the path and file name.
5. F5
6. Look at the results in the active sheet of the workbook
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
robmarr700Author Commented:
Just some quick observations.

I have tested source file 3.txt

I have noticed a number of duplicates for product group 'ZEP'



ZEP      ZX01SIZE10                   Premium Safety Chukka Boot Size 10                                    4
ZEP      ZX01SIZE11                   Premium Safety Chukka Boot Size 11                                    2
ZEP      ZX01SIZE8                    Premium Safety Chukka Boot Size 8                                    7
ZEP      ZX01SIZE9                    Premium Safety Chukka Boot Size 9            2                        5
ZEP      ZX10SIZE10                   Safety Chukka Boot Size 10                                        4
ZEP      ZX10SIZE11                   Safety Chukka Boot Size 11                                        2
ZEP      ZX10SIZE8                    Safety Chukka Boot Size 8                                         7
ZEP      ZX10SIZE9                    Safety Chukka Boot Size 9                                         7
ZEP      ZX15SIZE10                   Safety Trainer Size 10                    1      1                  2
ZEP      ZX15SIZE11                   Safety Trainer Size 11                          1                  1
ZEP      ZX15SIZE8                    Safety Trainer Size 8                                             6
ZEP      ZX15SIZE9                    Safety Trainer Size 9                                             7
ZEP      ZX17SIZE10                   Premium Safety Work Boot Size 10                                    4
ZEP      ZX17SIZE11                   Premium Safety Work Boot Size 11                                    2
ZEP      ZX17SIZE8                    Premium Safety Work Boot Size 8                                    7
ZEP      ZX17SIZE8                    Premium Safety Work Boot Size 8                                    7
ZEP      ZX17SIZE8                    Premium Safety Work Boot Size 8                                    7
ZEP      ZX17SIZE9                    Premium Safety Work Boot Size 9                                    7
ZEP      ZX17SIZE9                    Premium Safety Work Boot Size 9                                    7
ZEP      ZX17SIZE9                    Premium Safety Work Boot Size 9                                    7
ZEP      ZX50SIZE10                   Premium Non Metallic Boot Size 10                                    4
ZEP      ZX50SIZE10                   Premium Non Metallic Boot Size 10                                    4
ZEP      ZX50SIZE10                   Premium Non Metallic Boot Size 10                                    4
ZEP      ZX50SIZE11                   Premium Non Metallic Boot Size 11                                    2
ZEP      ZX50SIZE11                   Premium Non Metallic Boot Size 11                                    2
ZEP      ZX50SIZE11                   Premium Non Metallic Boot Size 11                                    2
ZEP      ZX50SIZE8                    Premium Non Metallic Boot Size 8                                    7
ZEP      ZX50SIZE8                    Premium Non Metallic Boot Size 8                                    7
ZEP      ZX50SIZE8                    Premium Non Metallic Boot Size 8                                    7
ZEP      ZX50SIZE9                    Premium Non Metallic Boot Size 9                                    7
ZEP      ZX50SIZE9                    Premium Non Metallic Boot Size 9                                    7
ZEP      ZX50SIZE9                    Premium Non Metallic Boot Size 9            1                        6
0
 
aikimarkCommented:
Did you clear the active worksheet before you ran your import?
0
 
aikimarkCommented:
You can have the import routine clear the contents and reset the used range before the import.
Dim vThing as Variant
activesheet.usedrange.clearcontents
vThing = activesheet.usedrange.address

Open in new window

0
 
robmarr700Author Commented:
No I didn't. Do I need to replace any lines from the coding with these 3 lines or do I just add them below line Public Sub Q_28502636()
0
 
aikimarkCommented:
these are new lines
0
 
robmarr700Author Commented:
Great, the macro seems to working as it should for all 3 test files. Could you help me with the steps I need to take to add a button to my master utility so I can click button>>choose txt. file>>>macro will run automatically.

Rob
0
 
aikimarkCommented:
@Rob

Please answer my earlier questions.
0
 
robmarr700Author Commented:
Sorry which questions?
0
 
aikimarkCommented:
look back in the prior thread.
0
 
robmarr700Author Commented:
I don't know which questions your referring to?
0
 
aikimarkCommented:
a. the ones you didn't answer
b. the ones related to the files
0
 
robmarr700Author Commented:
Yes I've compared by latest parsed results against my expected results. Everything seems to be okay.
0
 
aikimarkCommented:
Answer these questions: http:Q_28502636.html#a40292511
0
 
aikimarkCommented:
Your request with help to "add a button" makes no sense if the participating experts don't know what the button is to do (the context of the problem solution).
0
 
aikimarkCommented:
Awaiting your comment
0
 
robmarr700Author Commented:
experts just a quick notice. I am now away on business for the next two weeks.

I haven't abandoned the question. I will leave another response when I am ready to resume.

Rob
0
 
aikimarkCommented:
@Rob,

We are now about two weeks past the end of your two week business trip.  Please return to this thread.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now