Skip to Content
author's profile photo Former Member
Former Member

Automatically filling the variant for workbook

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jul 11, 2007 at 08:39 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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?

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.