Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2008
    Posts
    78

    Unanswered: Modifying Macro Parameters in Access/VB

    Inside an event (Button Press) I have code that includes this statement:

    DoCmd.RunMacro "SBUX EODProblemReportCounts"


    That macro copies a query to an xls file that I later mail to a distribution list.

    Is there any way to get to the Action Arguments before calling it?

    For example, I'd like to decide on the "Output File" argument at runtime, although it is stored in the Action Arguments currently.

    The reason is that based on a particular report, I would want to change the output filename/directory.

  2. #2
    Join Date
    Sep 2008
    Location
    Amherst NY (near Buffalo)
    Posts
    11
    You could, but it would be MUCH easier if you would convert this macro over to VBA code and then just specify the parameters yourself while running the DoCmd.TransferSpreadsheet command.

  3. #3
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by Amicron
    You could, but it would be MUCH easier if you would convert this macro over to VBA code and then just specify the parameters yourself while running the DoCmd.TransferSpreadsheet command.
    I'm new in access and wasn't aware of this function. All I really need to do is specify the output filename at runtime. In looking up the DoCmd.TransferSpreadsheet Method here:

    http://msdn.microsoft.com/en-us/library/bb214134.aspx

    it doesn't look like I can give it a query. If this method does support a query can you give me a syntax example?

    Query Name "OrderRollUp"
    Output Type: Microsoft Excel 97-2003 (*.xls)
    Output Filename: (this will be stored in a variable at runtime)

    I'd much prefer to do it in code than call a macro if possible. Thanks for the suggestion

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    extract from the URL you pointed to:
    TableName A string expression that is the name of ... the Access select query whose results you want to export to a spreadsheet.

    dim someStringVariable as string
    someStringVariable = "c:\here\there\my.xls"
    docmd.transferspreadsheet acExport,,"OrderRollUp", someStringVariable

    (double comma is not a typo)

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Sep 2008
    Location
    Amherst NY (near Buffalo)
    Posts
    11
    Thank you izyrider. My thoughts exactly.

  6. #6
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by izyrider
    extract from the URL you pointed to:
    TableName A string expression that is the name of ... the Access select query whose results you want to export to a spreadsheet.

    dim someStringVariable as string
    someStringVariable = "c:\here\there\my.xls"
    docmd.transferspreadsheet acExport,,"OrderRollUp", someStringVariable

    (double comma is not a typo)

    izy
    I see it further down in the text thank you. The parameter specified was "TableName", and didn't see that this applied to queries as well. It was about 1:30 in the morning late Sunday and I get up at 6am for work... I appreciate your help, too, Amicron.

  7. #7
    Join Date
    Sep 2008
    Location
    Amherst NY (near Buffalo)
    Posts
    11
    NP. That's what you get for coding so EARLY in the morning - and by EARLY I mean that most serious coders (geeks like me) don't even get STARTED until about 2am. Ha ha ha.

  8. #8
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by Amicron
    NP. That's what you get for coding so EARLY in the morning - and by EARLY I mean that most serious coders (geeks like me) don't even get STARTED until about 2am. Ha ha ha.
    hahah lucky you. it would be easier if i was a programmer and could code at work but I'm not. i just know that it makes my job a lot easier to learn this, so I end up doing most of it at night and weekends. i've been using access less than a month and it's not bad.

    btw i just got it working and the DoCmd.TransferSpreadsheet works like a champ and is WAY more flexible than running a macro. thx for pointing me in the right direction

Posting Permissions

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