Errrg
This may sound stupid, but when I try to step into the code with F8, I get that annoying 'Bell' sound. It just won't let me. When I hover the cursor over the highlighted area in debug, nothing comes up. I have attached the complete code for both the Form and the Module:
I really appreciate your patience!
Form:
Private Sub cmdGO_Click()
Dim strFilePath As String
Dim strFileName As String
Dim strFileTemplate As String
Dim strMacroName As String
'''''''''''''UPDATE THIS DATA WITH YOURS''''''''''''''''''''''''''''''
'Fill in the following with your files and path
strFilePath = "\\MINT02\Share\SHARE\SMP\DialerbyTeam\"
strFileName = "DialerbyTeamCurrent.xls"
strFileTemplate = "DialerbyTeamCurrent.xls"
strMacroName = "Update"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This deletes the old file
'Kill strFilePath & strFileName
'This recreates your file with the template
'FileCopy strFilePath & strFileTemplate, strFilePath & strFileName
'This is a custom function I built to set Excel as an object and you can access/export
'to a workbook programmatically. 'openexcel' is stored in a module called Functions.
'This will open the new file that was created previously
openexcel strFilePath & strFileName
'''''''''''''UPDATE THIS DATA WITH YOURS''''''''''''''''''''''''''''''
'Export data is another function that will export your data.
'Update the query Names to your real Query Names
'Update the Sheet Names accordingly, with the Query it is assiged to
ExportData "01_HRS_POOL_TEAM", "HR_TEAM_POOL"
ExportData "01_HRS_POOL_TEAM", "HR_COLLID"
ExportData "01_HRS_POOL_TEAM", "HRS_TEAM"
ExportData "TEAM_HR_USER_EXP", "AVG_HRS_COLL"
ExportData "02_HRS_Utilization", "TimeUtilization"
'ExportData "QUERY4", "SHEET4"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
xl.ActiveWorkbook.Save
'The Application.Run will run the Macro(s) that you saved in your spreadsheet
xl.Application.Run "'" & strFileName & "'!" & strMacroName
xl.ActiveWorkbook.Save
'Uncomment/Comment these to close out the workbook
'xl.ActiveWorkbook.Close
'xl.Quit
Set xl = Nothing
End Sub
Private Function ExportData(strQuery As String, strSheet As String)
Dim intR As Integer
Dim rs As Recordset
'After you open that Object/Workbook, you refer to that workbook now as 'xl'. You will
'use it later, but now you have to access your queries through this code and to do so
'you need to use a recordset.
'strQuery is the name of the Query that you passed with the Function. You can also
'use an SQL string.
Set rs = CurrentDb.OpenRecordset(strQuery)
rs.MoveLast 'moves to the last record
rs.MoveFirst 'moves back to the first record
'You can use record count to make sure there are records in your Query/Recordset
If rs.RecordCount < 1 Then
'There are no records
MsgBox "There are no records for " & strQuery
Else
'There are 1 or more records. Now Select the sheet that you will be exporting to
xl.Sheets(strSheet).select
'Now you need to loop through the records. 'intR' was dimmed at beginning of this
'function and will now use it to create a loop or 'For, Next'
'Starts with record 1 and gets the count of records in the recordset so it knows where
'to stop.
For intR = 1 To rs.RecordCount
'Now we need to export the recordset/query to the workbook/object we opened earlier.
'Remember 'rs' refers to the recordset & 'xl' refers to the workbook
'xl.cells(ROW,COLUMN).VALUE = rs.fields(INDEX).
'This is how you will fill in the value of a cell on the workbook. For the ROW you
'will want to add + 1 if you have Headings on your sheet. The INDEX for rs.fields
'refers to the columns of the recordset/query. The first column of the recordset
'starts with the index of zero.
xl.cells(intR + 1, 1).Value = rs.Fields(0)
xl.cells(intR + 1, 2).Value = rs.Fields(1)
xl.cells(intR + 1, 3).Value = rs.Fields(2)
xl.cells(intR + 1, 4).Value = rs.Fields(3)
xl.cells(intR + 1, 5).Value = rs.Fields(4)
'If there are more columns to add than this just follow what I have above
'Moves to the next record
rs.MoveNext
Next intR 'Loops back to For and enters data for the next row
'Once the export is done, this just puts the cursor to A1 on each sheet
xl.range("A1").select
'Clears the recordset
rs.Close
Set rs = Nothing
End If
End Function
Module (Function)
Option Compare Database
Option Explicit
Public xl As Object
Function openexcel(strLocation)
Set xl = CreateObject("Excel.Application")
xl.Visible = True
xl.Workbooks.Open strLocation
End Function
