Pass variable to excel from access?

[John64]

I am attempting to execute some excel macros from access. I have had it all run successfully with a single variable stored in the excel macros, but I would like to change a few variables from access and make them recognizeable in the excel macros. I believe this is very simple to someone that understands the public and private relationships of this stuff but I just can't get it to work. Here is the problem.

 Dim appXL As Excel.Application
Dim wbk As Excel.Workbook
Set appXL = New Excel.Application
Set wbk = appXL.Workbooks.Open("F:\Path")
With appXL.Application
    strfieldman = "Ashlee"
    .Run "'FIELDMEN Baker.xlsm'!UpdateList.UpdateList"
    .Run "'FIELDMEN Baker.xlsm'!ImportIt"
    .Run "'FIELDMEN Baker.xlsm'!SuddenChange"
End With
With appXL.Application
    .Run "'FIELDMEN Baker.xlsm'!Mail"
End With

The variable 'strfieldman' isn't being passed to the excel macros so my sql statement in the macro is coming out blank. I don't know the term for what I am trying to do, that is why I haven't been able to figure this out. Thanks in advance.

 

[Uncle Gizmo]

Can you post the code where strfieldman is being used?

 

[John64]

I got it all working now by just using excel for the whole thing and defining a public variable within excel to pass between the 4 or 5 macros that I needed. I still don't know why the variable wouldn't carry over to excel from access. The code below is the sql string the variable was used in. I tried many simple message boxes to see if the variable was ever being transferred between excel and access and I never had any luck. Messagebox for access would be correct and the variable was public but it would show up black once excel launched a message box. Maybe this just isn't a supported function. In the end I just created a sheet inside my excel workbook that would store the list of emails and names that I needed.

sql = "SELECT  [Welldata Table].WellNo, [Welldata Table].WDone, [Welldata Table].PrForm, [Welldata Table].Fieldman, [Welldata Table].Q2, [Welldata Table].Q1, [Welldata Table].Sec, [Welldata Table].Twp, [Welldata Table].Rng, [Welldata Table].ProdType, [Welldata Table].TTD, [Welldata Table].TSize, [Welldata Table].Measurement " _
        & "FROM [Welldata Table] " _
        & "WHERE  [Welldata Table].Measurement='PAS' AND [Welldata Table].Fieldman='" & [COLOR=red]fieldmanVar [/COLOR]& "' " _
        & "ORDER BY [Welldata Table].WellNo;"

 

[kennedyusa]

Great work .. really informative .. and thanks a lot for sharing

calcul credit immobilier courtier taux simulation de france pretUne simulation credit immobiler de France a faire un pret.calcul credit immobilier courtier taux simulation de france pret