Want to try this code as a function

[Skip Bisconer]

I replacing data periodically into a linked Excel file and depending on the month the analysis is taking place it uses a different query as a record set. If I could change the record set with a case statement and use this as a function that would be nice. I just don't know if it can be done.

Function ExportQueryToLocation1()
On Error GoTo ErrorHandler
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Set objXL = CreateObject("Excel.Application")
objXL.Visible = False
Set xlWB = objXL.Workbooks.Open("C:\Inventory\InventoryAnalysisLoc1")
Set xlWS = xlWB.Worksheets("Location1")
 
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
[COLOR=red]Set rs = db.OpenRecordset("Usage1P1", , dbOpenDynamic) [/COLOR]
[COLOR=red]' Can this be set in a Case statement?[/COLOR]
rs.MoveFirst
i = 2
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i).Value = rs.Fields("Field1").Value
  .Range("B" & i).Value = rs.Fields("Field2").Value
  .Range("C" & i).Value = rs.Fields("Field3").Value
  .Range("D" & i).Value = rs.Fields("Field4").Value
  .Range("E" & i).Value = rs.Fields("Field5").Value
  .Range("F" & i).Value = rs.Fields("Field6").Value
  .Range("G" & i).Value = rs.Fields("Field-etc").Value
  .Range("H" & i).Value = rs.Fields("Field-etc").Value
  .Range("I" & i).Value = rs.Fields("Field-etc").Value
  .Range("J" & i).Value = rs.Fields("Field-etc").Value
  ' etc
  End With
i = i + 1
rs.MoveNext
Loop
 
'Manipulate Excel
objXL.Application.Run "InventoryAnalysisLoc1!CalcOrderPoint"
objXL.activeworkbook.Save
objXL.Application.Quit
MsgBox "New data has posted."
 rs.Close
 db.Close
 Set xlWB = Nothing
 Set xlWS = Nothing
 Set objXL = Nothing
 
 Exit Function
ErrorHandler:
    ' Display error information.
    MsgBox "Error number " & Err.Number & ": " & Err.Description
    ' Resume with statement following occurrence of error.
    Resume Next
 
End Function

 

[pbaldy]

Use a different query based on month? It would be fairly simple:

Dim strQuery as String

Select Case Month(Date())
  Case 1
    strQuery = "QueryToUseForJanuary"
  Case 2
    strQuery = "QueryToUseForFebruary"
...
End Select

Set rs = db.OpenRecordset(strQuery, , dbOpenDynamic)

 

[Skip Bisconer]

Thanks Paul.
This question will indicate my lack of knowledge. Do I use this in the Option box case statement or in the function module?

 

[pbaldy]

If the choice is to be determined automatically based on the date, put it in the function. If the user is to pick the month, either do it in the option group code and pass the name of the query to the function, or just pass the value of the option group to the function and do it there.