Set MyValue

[bodhisatvaofboogie]

I wish to open a file within a macro of Access. I want to be able to select
the file that is output to as the file to open. I figured I could find a way
to set the MyValue as the output file, but I'm not as familiar with Access
code as I am with Excel, so Help would be appreciated. The codes are as
follows.


Function TESTING()
On Error GoTo IMPORT_STOCK_STAT_Err

DoCmd.SetWarnings False
DoCmd.OpenQuery "DR037074 QUERY", acViewNormal, acEdit
DoCmd.OpenQuery "SS037074 QUERY", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acImport, 8, "SS037074",
"G:\Monthly_Reports\8888888\Stock_Status_Reports\SS8888888.XLS", True, ""
DoCmd.TransferSpreadsheet acImport, 8, "DR8888888",
"G:\Monthly_Reports\8888888\Machine_Down_Dealer_Reports\Monthly_Machine_Down_(Dealer_8888888).xls", True, ""
DoCmd.OutputTo acQuery, "AUDIT", "MicrosoftExcelBiff5(*.xls)",
"G:\Monthly_Reports\8888888\MDA_WORD_FORM\MDA(Dealer_8888888).xls", False,
"", 0
DoCmd.SetWarnings True


IMPORT_STOCK_STAT_Exit:
Exit Function

IMPORT_STOCK_STAT_Err:
MsgBox Error$
Resume IMPORT_STOCK_STAT_Exit

End Function

Now I want the output destination to be set to MyValue so that I can plug it
into this code, or if there is a better way to open that output file, I'm all
eyes!! THANKS!!!


Function OpenEXCEL()



Dim xlApp As Variant
Dim xlBook As Variant
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.WorkBooks.Open("MyValue") ' open destination file
here
xlApp.Application.Visible = True
xlApp.Application.DisplayAlerts = False
xlApp.WorkBooks.Open "C:\Documents and Settings\8888888\Application
Data\Microsoft\Excel\XLSTART\MACROTESTING.XLS"
xlApp.Application.Run ("MACROTESTING.XLS!MDAFORMATFINAL")
xlApp.WorkBooks("MACROTESTING.XLS").Close
xlBook.Close (False)
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
End Function

 

[John W. Vinson]

Now I want the output destination to be set to MyValue so that I can plug it
into this code, or if there is a better way to open that output file, I'm all
eyes!! THANKS!!!

Just pass it as a parameter to the function:

Function TESTING(OutputFileName As String)
On Error GoTo IMPORT_STOCK_STAT_Err

DoCmd.SetWarnings False
DoCmd.OpenQuery "DR037074 QUERY", acViewNormal, acEdit
DoCmd.OpenQuery "SS037074 QUERY", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acImport, 8, "SS037074",
"G:\Monthly_Reports\8888888\Stock_Status_Reports\SS8888888.XLS", True, ""
DoCmd.TransferSpreadsheet acImport, 8, "DR8888888",
"G:\Monthly_Reports\8888888\Machine_Down_Dealer_Reports\Monthly_Machine_Down_(Dealer_8888888).xls",
True, ""
DoCmd.OutputTo acQuery, "AUDIT", "MicrosoftExcelBiff5(*.xls)",
OutputFileName, False,
"", 0
DoCmd.SetWarnings True


IMPORT_STOCK_STAT_Exit:
Exit Function

IMPORT_STOCK_STAT_Err:
MsgBox Error$
Resume IMPORT_STOCK_STAT_Exit

End Function

and then call the function

Dim MyValue As String
MyValue = "K:\Output Location\July.xls"
Dummy = TESTING(MyValue)

Note that you're using a Function but not returning any value - that's ok, but
why not use a Sub instead and just call it?

John W. Vinson [MVP]