Automatically filling the variant for workbook

[Former Member]

Hi, We have a requirement to create workbook for 3000 customers for their sales data and workbook require variable "customer number". So we will be require to create the spreadsheet for 3000 or more customers at a given day automatically (that mean 3000 workbook copies each for every customer).

How can we feed all 3000 or more customer numbers to this workbook one by one to generate 3000 copies of workbook for each customer?

Any help will be appreciated.

Steve

[Former Member · Accepted solution]

Hi Steve,

Am not sure if I got your requirement correctly. Anyways let me try to help.

1. Is it not possible to create the query in workbook to accept the customer number as a filter value and each customer refresh the query entering their customer number ?

2.

-

---

Pull the list of customer numbers to a temporary sheet

-

---

Create a VBA Macro that loops through the list of customers.

-

---

For the current customer in loop use the following sapbex.xla functions to apply filter and refresh the query.

-

---

Run "SAPBEX.XLA!SAPBEXsetFilterValue", customernumber, , Sheets("Querysheet").Range("col:row"))

-

---

Run "SAPBEX.XLA!SAPBEXrefresh", False, Sheets("QuerySheet").Cells(3, 1)

-

---

Once the query is refresheshed use the following code to save the current workbook in another name.

ActiveWorkbook..SaveCopyAs <WorkbookName+CustomerNumber>

More info on sapbex features is available at: http://help.sap.com/saphelp_bw21c/helpdata/en/f1/0a55f9e09411d2acb90000e829fbfe/frameset.htm

Please let me know if it helps

Regrads,

Rit

[Former Member · Accepted solution]

Thanks Rit, I will try to use 2nd option (VBA Macro) since we are trying to create thousands of workbooks automatically for all customers. I am not good at VBA script and macro but I will try. I wouldn't able to open the below link:

http://help.sap.com/saphelp_bw21c/helpdata/en/f1/0a55f9e09411d2acb90000e829fbfe/frameset.htm

I will let you know if 2nd option (VBA Macro) will work.

Steve

[Former Member · Accepted solution]

Hi Rit,

This code is not refreshing any queries:

Run "SAPBEX.XLA!SAPBEXrefresh", False, Sheets("QuerySheet").Cells(3, 1)

When I set it to true, then it is prompting user to enter customer number.

Run "SAPBEX.XLA!SAPBEXrefresh", TRUE, Sheets("QuerySheet").Cells(3, 1)

How to set the filter value? please advice if I am using right code below for first 20 customers, below codes are prompting user to enter the value. Customer list is in sheet2:

Sub GenerateRPT()

Dim Counter As Integer

Dim curCell As String

For Counter = 1 To 20

curCell = Worksheets("Sheet2").Cells(Counter, 3)

Run "SAPBEX.XLA!SAPBEXsetFilterValue", curCell, , Sheets("Sheet1").Range("A56:I56")

Run "SAPBEX.XLA!SAPBEXrefresh", True, Sheets("Sheet1").Cells(4, 2)

ActiveWorkbook.SaveCopyAs "c:\Salesreport_" & curCell & ".xls"

Next Counter

End Sub

Thanks,

Steve

[Former Member · Accepted solution]

Hi Steve,

1) -

---

The format for SPBEXrefresh is as follows: (as per help.sap )

Function SAPBEXrefresh(allQueries As Boolean, Optional atCell As Range) As Integer

allQueries = FALSE: Refreshing the query with cell atCell (if this parameter is missing, then the active cell is taken as the basis). In this variant, the function produces a context error, if atCell does not belong to a results area or is a filter cell.

Since you have only one query in the workbook you can give the first parameter as TRUE.If you want to run a query to fetch the customer numbers first and then the original query then you can use FALSE.

When u use FALSE please point the atCell parameter to the cell where the query name is entered. This will make sure that the query is refreshed.

2) To set Filter value

Function SAPBEXsetFilterValue(intValue As String, Optional hierValue As String, Optional atCell As Range) As Integer

Filtering with cell atCell (if this parameter is missing, then the active cell is taken as the basis). The internal filter value must be entered in the parameter intValue.

Enter the parameter hierValue when dealing with a node of a certain characteristic hierarchy with the filter value.

" intValue = <blank> " means "do not filter anymore"

The atCell should point to the filter cell in the query sheet where the Customer number appears when you run the query normally. Run the query manually, enter a customer number, after the query gets refreshed note down the cell number where the customernumber is shown. provide this cell number as the input to setFilterValue.

Let me know if it works.

Regards

[Former Member · Accepted solution]

Thanks Rit for the information. It worked. I have four queries in workbook using same filter "Customer Number", three of them have customer number displayed on work book and it worked well for three of them but for 4th query I don't see customer number filter displayed on workbook and it is not working. query definition have two structures (one in column and one in row), I have customer number in filter section of query definition and I cannot drage and drop customer number to row area of query definition since it is already placed in filter section. If I remove from filter then I can drage and drop to row section of query.

I am not sure how to deal with this but at least three queries getting refreshed in workbook out of four.

Thanks again for your help.

Steve

[Former Member · Accepted solution]

Hi Steve,

Try the following BEx Analyzer menu option:

BusinessExplorer > Layout > Display Text Elements > All

Check if the workbook is displaying customer number filter after you do this.

Regrads,

Rit

Message was edited by:

Rithesh Vijayakrishnan

[Former Member · Accepted solution]

Hi,

I am working on a similar requirement where I need to generate around 100 workbooks for different facilities. I have do not have much knowledge on macros so finding it difficult to achieve this. can you please help me?