excel binding issue i think...

[trevor40]

I have this code below to create an excel workbook, it spits out the queries and saves the file in the first procedure, then the next procedure opens the workbook for formatting.

this all works fine on one pc but not another, they have identical windows and office versions, same n/w access.
On the bad pc it opens 2 copies of excel, both with blank windows (no worksheet displayed), when the code finishes they are still both blank.
the line of code that fails is shown in red, as no worksheet is visible it cannot select the worksheet.

Can anybody see what may be wrong with the sub - format_sheets_now()



Private Sub Command94_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim gg As String
sdt = Format(start_date, "dd-mm-yy")
edt = Format(End_date, "dd-mm-yy")
gg = "C:\aaa\timesheets\Employee Time Report master.xls"
Set db = CurrentDb()
Set rs = db.OpenRecordset("create excel time sheets for selected employees on main menu")
With rs
.MoveFirst
Do While Not .EOF
fn = rs.Fields("First Name")
Ln = rs.Fields("Last Name")
bc = rs.Fields("Barcode")
Me.barcode = bc
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "print time sheets for selected employees", gg, True, fn & " " & Ln
DoEvents
.MoveNext


Loop
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Call format_sheets_now
T = MsgBox("All requested Excel Time Sheets have been created" & vbCrLf & " And saved in the following directory" & vbCrLf & vbCrLf & " C:\aaa\timesheets", vbOKOnly, "Automated Time Sheet Generation")


Sub format_sheets_now()

Dim ws As excel.Worksheet
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strPath As String
Dim stDocName As String
'Early binding
'Dim Excel_Application As excel.Application
'late binding
Dim Excel_Application As Object
Dim Excel_Workbook As Workbook
Dim Current_Worksheet As Worksheet
Dim gg As String
gg = "C:\aaa\timesheets\Employee Time Report master.xls"
sdt = Format(start_date, "dd-mm-yy")
edt = Format(End_date, "dd-mm-yy")
Set Excel_Workbook = GetObject(gg)
Set Excel_Application = Excel_Workbook.Parent
Set Current_Worksheet = Excel_Workbook.Worksheets(1)
Excel_Application.WindowState = xlMaximized
Excel_Application.Visible = True
Excel_Workbook.Windows(1).Visible = True
Excel_Workbook.Worksheets(1).Select
Excel_Application.DisplayAlerts = False
Excel_Workbook.SaveAs filename:="C:\aaa\timesheets\Employee Time Report for - " & sdt & " to " & edt & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Excel_Application.DisplayAlerts = True

code contiues...

[trevor40]

It seems that I got it wrong, the PC that this is NOT working on is a Windows 8 system. Both access versions are the same. I think I have set all security options correctly in both Access and Excel.

[trevor40]

All fixed. thanks anyway, I did this and it's ok now.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim Excel_Application As excel.Application
Dim Excel_Workbook As Workbook
Dim gg As String
gg = "C:\aaa\timesheets\Employee Time Report master.xls"
Set Excel_Workbook = Workbooks.Open(gg)
Set Excel_Application = Excel_Workbook.Parent
Excel_Application.Visible = True
Excel_Workbook.Worksheets(1).Select