Loop Problem

[bee]

Hi there.

I would like to ask for some help regarding a loop. I'm new with VBA and I would like to ask if anyone could help me produce the output that is required. Attached are the mdb file where I coded the program and the workbook. The required output is the output worksheet. I just have to be able to produce that getting the data from Source Pivot, that contains the breakdown and Internal Pivot, that contains the total. I've already coded the first part of the program. I'm having a hard time producing the output.

Thanks so much for looking into my code.

[OBP]

For someone new to VBA you seem to be doing very well to me.
But I don't understand what you are actually doing.
I can see that you have the Excel workbook with various worksheets of data.
I can see that you have the Form in the Access database with the VBA Code to manipulate the Excel data.
What I don't understand is why you are using Access, as there are no tables in the database. Why aren't you doing the VBA in the Excel Workbook?
Or should the Access database have tables, which you have not provided?
You appear to be Exporting Access data to Excel in this section -
Worksheets("Within CMB2").Range("A1:A1") = "DETB_UPLOAD_DETAIL"
Worksheets("Within CMB2").Range("A2:A2") = "BATCH_NO"
Worksheets("Within CMB2").Range("B2:B2") = "BRANCH_CODE"
but why, when Access does Pivot Tables and Crosstab queries.

[bee]

I did not include the tables in my MS Access because I don't think it's relevant to do so. :) It might get confusing. What I'm trying to do is to generate a worksheet, named Within CMB2 which is the exact duplicate of Output worksheet. Output worksheet is just an example. I mean it should be the output of the program. :)

Thanks for looking into my code. I really did research to be able to work on that. Hehe.


Attached is the doc that contains the information needed. :) it states where each content of the field can be found in the Source pivot.

Thanks again. I really appreciate the help.

[OBP]

So does that mean you are actually trying to Insert 5 columns of data into the existing "Output" worksheet, leaving the other data in place?
Or all of the columns?
An alternative strategy would be to arrange the data in a Query and then "Transfer Spreadsheet".

[bee]

So does that mean you are actually trying to Insert 5 columns of data into the existing "Output" worksheet, leaving the other data in place?
Or all of the columns?
An alternative strategy would be to arrange the data in a Query and then "Transfer Spreadsheet".
Uhhm not just 5 columns but those are the ones that I am having problems with inserting. I don't know how to create a loop that will first check the Cost Center, if it's 2355 then, arrange the Inv Ref alphabetically so that 2008 DTI 05 SG ARR will be the first entry. It will also check the non zero entries under a certain Inv Ref, so it started with the value 1,004.50. From the word docu, I just showed where the values came from (Source Pivot). But the final output will still look exactly like Within CMB worksheet.

I'm not familiar with the Query and Transfer Spreadsheet. Hehe. Sorry. But thanks again for your help. :)

[OBP]

Is Inv Ref in your Access table?
If so then your data sorting, grouping and filtering should be done in a query and the Query used for your data transfer process.
So you would open a Recordset based on your Query and then Check how many records you have in total.
Create a loop to move through the records and place the data on your Worksheet, which presumably you would reset to just the Headings before you started.

[bee]

Is Inv Ref in your Access table?
If so then your data sorting, grouping and filtering should be done in a query and the Query used for your data transfer process.
So you would open a Recordset based on your Query and then Check how many records you have in total.
Create a loop to move through the records and place the data on your Worksheet, which presumably you would reset to just the Headings before you started.

It's not in the Access table. :( It's just part of the Excel sheet that another party is sending to us.

[CreganTur]

You could make the excel sheet from your vendor into a linked table. This would allow you to run a query on it exactly the way that OBP suggests, which I think would be an easy solution for you, since it would allow you to control your data easier via a query.

Take a look at this link: http://vbaexpress.com/kb/getapprovalarticle.php?kb_id=1012 This is a kb entry of mine that's awaiting approval. It will let you choose a spreadsheet via a file dialog window and it will make it into a linked table. Then you can query the table.

You'll have to do some retrofitting to get it to work for you, but the first 3 sections should suit you perfectly- the ones that let you select the file via file dialog, grabs the spreadsheet names, and creates a linked table.

I know this doesn't answer your whole problem, but it will allow you to use OBP's idea, which seems very sound :sleuth:

[OBP]

Cregan Tur, that sounds like a great KB Article, I will have a look and keep a Link to it for future references. :beerchug:

[CreganTur]

Cregan Tur, that sounds like a great KB Article, I will have a look and keep a Link to it for future references. :beerchug:

Thanks man! Here's hoping for approval! :beerchug:

[bee]

You could make the excel sheet from your vendor into a linked table. This would allow you to run a query on it exactly the way that OBP suggests, which I think would be an easy solution for you, since it would allow you to control your data easier via a query.

Take a look at this link: http://vbaexpress.com/kb/getapprovalarticle.php?kb_id=1012 This is a kb entry of mine that's awaiting approval. It will let you choose a spreadsheet via a file dialog window and it will make it into a linked table. Then you can query the table.

You'll have to do some retrofitting to get it to work for you, but the first 3 sections should suit you perfectly- the ones that let you select the file via file dialog, grabs the spreadsheet names, and creates a linked table.

I know this doesn't answer your whole problem, but it will allow you to use OBP's idea, which seems very sound :sleuth:

I will try your solution. I'm not really familiar with VBA's recordset but I will do my best. Thanks for your helpp. :) Will tell if I encounter problems.

[bee]

I tried your code, it did not work becuase I forgot to mention I'm using MS Access 2000 so the file dialog box properties don't work. I'm not sure what to do anymore. I don't know how to modify the code in such a way that it will be applicable for the file dialog box for MS Access 97/2000. :(

[CreganTur]

I tried your code, it did not work becuase I forgot to mention I'm using MS Access 2000 so the file dialog box properties don't work

Did you set a reference to Microsoft Office 11.0 Object Library? 10.0 might work for your version of access too if you don't have 11.0 available. File Dialog won't work in any version of Access if you don't set a reference.

[bee]

Did you set a reference to Microsoft Office 11.0 Object Library? 10.0 might work for your version of access too if you don't have 11.0 available. File Dialog won't work in any version of Access if you don't set a reference.

I only have Microsoft Office 9.0 Object in my reference. :(

[bee]

I've manage to gather the information already. I was able to create a worksheet similar to Within CMB yet it won't display the correct results. If anyone is available to check my code, can you help me fix it. I want the worksheet Within CMB2 become an exact duplicate of Within CMB. The source of the information comes from 2 worksheets, Internal Pivot and Source Pivot. I was able to start the code yet it's not producing the results that I want. I think something is wrong with my loop. Can you please please check? Thank you very much. I appreciate your help and time.

Attached is the access database where I've written my code and the source excel file.