How to send parameter from ms access to excel workbook
I 'm developing MS Access application (GUI & Database used VBA programming ), but i sending report to MS Excel template. when user want to view that report .them will click button as open workbook from MS Accecc user interface. system will open workbook & pop-up from wait for user select input, submit and system will receive data from MS Access Database output to that worksheet.
And now I need update that appication by select data item from listView from MS Access user interface and sendind some parameter for queries data same listview item to MS Excel template. On workbooks openning VBA will used parameter from MS Access and queries data same parameter from Database
Help me please
Thank.
How to send parameter from ms access to excel workbook ?
JRLiem
Hi
When you open Excel, in your MS VBA you have your values, the VBA in Excel is seperate, the two applications have peperates scopes for there variables.
If you want to pass values to the Excel VBA try writing them into cells in the spreadsheet from Access. Alternatively keep all your code in Access and control the Excel spreadsheet from there
Regards
ADG
dantheriver
Thank..
i try to coding as code below, but the system can't send that parameter, msgbox return null value
++++ MS Access Code ++++
Option Explicit
Public Type GetAppMonthly
varStartDate As String
varFinishDate As String
End Type
Public GetDataMonthly As GetAppMonthly
Public Sub ExcelReport(ByVal ExcelFiles As String)
Dim appEx As Excel.Application
Set appEx = CreateObject("Excel.Application")
If (ExcelFiles <> "") Then
appEx.Application.Visible = True
appEx.Workbooks.Open Filename:= _
"" & ExcelFiles
GetDataMonthly.varStartDate = FormatDateTime(Date, "dd/mm/yyyy")
GetDataMonthly.varFinishDate = FormatDateTime(Date, "dd/mm/yyyy")
End If
Set appEx = Nothing
End Sub
Private Sub Cmd1_Click()
Call subExcelReport("C:\Temp\xlsTest.xls")
End Sub
++++ MS Excel Code ++++
Option Explicit
Public Type GetAppMonthly
varStartDate As String
varFinishDate As String
End Type
Public GetDataMonthly As GetAppMonthly
Private Sub Workbook_Open()
GetDataMonthly.varStartDate=GetDataMonthly.varStartDate
GetDataMonthly.varFinishDate=GetDataMonthly.varFinishDate
MsgBox GetDataMonthly.varStartDate & "," & GetDataMonthly.varFinishDate
End Sub
kundalani
Hi,
I think you can first open the workbook via MS Access in VBA. Then you are free to call the functions in Excel, or you can control Excel from Access.
What you have to do is first create the Excel object.
1. Add the Excel X Object Library (Where X is the version of Excel) in VBA IDE
2. Create an instance of Excel object in your code
Dim objExcelApp as Excel.Application
Set objExcelApp = New Excel.Application
3. Then open the workbook via VBA
Now, you can do the things that you want to the Excel workbook.
Hope this can help you.