Public Sub ExportTaxWorkforceData(strPeriod As String, strPath As String)
Dim strExcelFile As String
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbooks
Dim xlWs As Excel.Worksheet
Dim rngCurr As Excel.Range
Dim rstAsADODB.Recordset
Dim strQuery As String
Dim strSQL As String
On Error GoToErrorHandler
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("" &strPath& "")
Set xlWs = xlWb("SetDates")
xlApp.Visible = True
xlWs.Range("B2").FormulaR1C1 = CInt(strPeriod)
Call Sleep(10000)
strQuery = "Learning Count"
strSQL = "SELECT * FROM [" &strQuery& "];"
Set rst = New ADODB.Recordset
rst.OpenstrSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Set xlWs = xlWb.Worksheets("Learning")
Set rngCurr = xlWs.Range("Testing")
rngCurr.CopyFromRecordsetrst
Set rngCurr = Nothing
Set rst = Nothing
Call Sleep(10000)
-------
xlWb.Save
xlWb.Close
xlApp.Quit
Set xlApp = Nothing
Set xlWb = Nothing
Set xlWs = Nothing
Set rst = Nothing
MsgBox "DONE!!!"
ExitRoutine:
Exit Sub
ErrorHandler:
If Not xlApp Is Nothing Then
xlApp.Visible = True
Set xlApp = Nothing
End If
If Not xlWb Is Nothing Then
Set xlWb = Nothing
End If
If Not xlWs Is Nothing Then
Set xlWs = Nothing
End If
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
Else
End If
MsgBoxErr.Description& " Error Number: " &Err.Number
Resume ExitRoutine
End Sub
I am getting Run time Error 3704, Please let me know where i am going wrong