Results 1 to 3 of 3
  1. #1
    RunTime91 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2014
    Posts
    281

    Running Excel Macro From Access Using Date Variable Entered In Access

    Greetings All ~

    I'm currently running an Excel macro from the click event on an Access UI.

    Below is the code I use to open the Excel Workbook containing the event procedure.
    Code:
        Dim AppExcel As Excel.Application
        Dim MyWorkbook As Excel.Workbook
            
        Path = "C:\Users\AA123456\Desktop\"
        MyFile = Path & "Foo.xlsm"
        
        Set AppExcel = CreateObject("Excel.Application")
        Set MyWorkbook = AppExcel.Workbooks.Open(MyFile)
        AppExcel.Visible = False
        AppExcel.Run "Module1.SalesRprts"
    The procedure in Excel calls the requested file with this code
    Code:
        Path = "N:\Sales Management\Internal\@DeptSales\Sales Reports\"
        MyFile = Path & "Regional_Sales_ & Format(Date, "yyyy-mm-dd") & "*.xlsx"
        Application.Workbooks.Open (MyFile)
    My question is this ~ Can I change the Date part of the above code to a date variable that is set in Access?


    Something like
    Code:
        Path = "N:\Sales Management\Internal\@DeptSales\Sales Reports\"
        MyFile = Path & "Regional_Sales_ & Format('" & AccessTextBoxValue & "', "yyyy-mm-dd") & "*.xlsx"
    As always thank you for any help

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I think you can pass arguments to the excel function

    Code:
    ...
    AppExcel.Run "Module1.SalesRprts", Me.TextBox
    In your excel vba modify the Module1.SalesRprts function to accept a date parameter

    Code:
    Public Sub SalesRprts(Optional FileDate As Date = Date)
       ...
        MyFile = Path & "Regional_Sales_" & Format(FileDate, "yyyy-mm-dd") & "*.xlsx"

  3. #3
    RunTime91 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2014
    Posts
    281
    Like a Dream!!

    Thank You Very Much kd2017!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 12-11-2020, 09:35 AM
  2. Replies: 2
    Last Post: 09-27-2017, 07:34 AM
  3. Running excel macro from access error
    By didiomm in forum Programming
    Replies: 2
    Last Post: 04-28-2016, 10:47 AM
  4. Running excel macro's from withing Access
    By zippy483 in forum Programming
    Replies: 5
    Last Post: 03-08-2011, 11:47 AM
  5. Running excel macro from access
    By timpepu in forum Programming
    Replies: 1
    Last Post: 02-26-2010, 11:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums