How can I get the value from a form in Access to Excel? 1

[Cogivina2004]

How would I code in excel marco in order to read the value of a form (Form!Ult!DY) in access?

 

[kphu]

I've never written code to grab data from a form in excel to access. Taking data from a form and sticking in excel is much easier.

I can see that there maybe a potential timing issue of grabbing data from a form.

While the user enters data into the form I would bet that the record may be locked so excel probably can't get to it.

Also once the record is saved, does the form clears? If so when will excel be triggered to grab the data?

The option you may have is to grab data from the source which is the table that the data is stored in.

Provide more info so that we can all brain storm.

ken

 

[Cogivina2004]

Private Sub Button_Click()

Dim objExcel As New Excel.Application
Dim objWorkbook As Excel.Workbook
Dim sourcefilepath As String, YYYYQ As String

DoCmd.SetWarnings False

YYYYQ = [Forms]![Ult]![DY] & [Forms]![Ult]![DQ]

DoCmd.OpenQuery "Delete TableTemp", acNormal, acEdit

sourcefilepath = "C:\MyFolder\Loss.xls"

Set objWorkbook = objExcel.Application.Workbooks.Open(FileName:=sourcefilepath)

objExcel.Run ("GetData")

''' I like to pass YYYYQ to Excel, so the macro GetData in Excel will know what data to use.

objWorkbook.Close
objExcel.Quit

Set objWorkbook = Nothing
Set objExcel = Nothing

DoCmd.Close acForm, "Ult", acSaveYes

End Sub

 

[kphu]

From the looks of it you'll have to write a function in excel for the macro "GetData" so that it takes YYYQ as an arguement.

I'm not sure what your "Getdata" macro does so its hard to write anything.

 

[mrmovie]

could you populate something in the excel spreedsheet?? a cell somewhere with the text you want to pass the marco?? the macro then reads the cell when it starts up??? not very nice but im not that experienced

 

[kphu]

yes you can do that.

I think this should work.

Private Sub Button_Click()
 
Dim objExcel As New Excel.Application
Dim objWorkbook As Excel.Workbook
Dim sourcefilepath As String, YYYYQ As String
 
DoCmd.SetWarnings False

YYYYQ = [Forms]![Ult]![DY] & [Forms]![Ult]![DQ]

DoCmd.OpenQuery "Delete TableTemp", acNormal, acEdit

sourcefilepath = "C:\MyFolder\Loss.xls"

Set objWorkbook = objExcel.Application.Workbooks.Open(FileName:=sourcefilepath)

objWorkbook.worksheets(1).range("A1").formula = YYYQ 
objExcel.Run ("GetData")

 
objWorkbook.Close
objExcel.Quit
 
Set objWorkbook = Nothing
Set objExcel = Nothing

DoCmd.Close acForm, "Ult", acSaveYes
 
End Sub

 

[Cogivina2004]

Sub GetData()
'
Dim dbt As Database, rst As Recordset
Dim MyType As Variant, AllTypes As Variant, YYYYQ as string

Application.DisplayAlerts = wdAlertsNone

YYYYQ = "20043"

AllTypes = Array("F", "R", "P")

Set dbt = OpenDatabase("C:\MyFolder\Loss.mdb")

For Each MyType In AllTypes

MyFileName = MyType & YYYYQ & ".xls"

Workbooks.Open Filename:=MyPathName & MyFileName

Set rst = dbt.OpenRecordset("LossTable", dbOpenTable)
......
......

The GetData macro in excel will open the workbook according the YYYYQ value (eg. F20043.xls). I like to retrieve this value from access form since that where I make a selection on DY and DQ, and not to hard code it in macro.

I hope this will help. Thanks.

 

[mrmovie]

somehow you are wanting to pass info from one application to another.
perhaps there is a public property in the Excel application object you can set? which you macro in the excel spreadsheet reads??

or you need to find a way of starting you sub and passing it a variable.
i think by the nature of COM objects (if this is the right word) it will simply not be possible. i read something once about API stuff which sounded similar.

anyway, its not a question of hardcoding anything into the sub routine. the suggestion of updating a cell dynamically for the sub to read should work.

if not, how about creating a registry entry which the sub reads? or a stampfile.txt file with info in it??

or you need to find a magic method of doing

objExcel.Run("GetData(variableB)") which i think you might be out of luck on.

 

[Cogivina2004]

.......
Set objWorkbook = objExcel.Application.Workbooks.Open(FileName:=sourcefilepath)

objWorkbook.wsListBoxValues.Range("IV1").Value = YYYYQ
.....

I get an error ("Object doesn't support this property or method").

 

[Cogivina2004]

My mistake. After I changed wsListBoxValues.range to worksheets("ListBoxValues").range, it works.

Thanks for all your helps.

 

[PHV]

Another way:
...
Set objWorkbook = objExcel.Workbooks.Open(FileName:=sourcefilepath)
objExcel.Run "GetData", YYYYQ
...
Sub GetData(YYYYQ As String)
Dim dbt As Database, rst As Recordset
Dim MyType As Variant, AllTypes As Variant
Application.DisplayAlerts = False
AllTypes = Array("F", "R", "P")
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Cogivina2004]

We can always count on you.

Thanks, PH.

 

[mrmovie]

i knew i should have stayed put in the vbscript forum!!