Results 1 to 4 of 4
  1. #1
    trevor40's Avatar
    trevor40 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    407

    Major issues with running on windows 8.1, All ok on win XP, 2003

    This code works fine on win XP , 7, 2003, Access 2003
    Major issue with running on windows 8.1, windows 7 both with Access 2010
    When i try to run this code it fails on the red line, excel hangs up with a blank excel screen, have to 'end task' to close it.

    Excel does start but only shows a blank screen.

    access debug error = select method of range class failed

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim strPath As String
    Dim stDocName As String
    Dim Excel_Application As Excel.Application
    Dim Excel_Workbook As Excel.Workbook
    Dim Current_Worksheet As Excel.Worksheet
    Dim Data_Range
    Dim Worksheet_Name
    Dim headercell, firstcellref, nextname, FirstName, linecount, nextcellref, bb, CC, dd, rangestart, rangeend, Mt
    Dim aa As Integer, gg As String
    Dim Counter As Integer
    sdt = Format(start_date, "dd-mm-yy")
    edt = Format(End_date, "dd-mm-yy")

    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")
    ns = rs.Fields("Normal Start Time")
    ne = rs.Fields("Normal End Time")
    bc = rs.Fields("Barcode")
    gg = "C:\aaa\timesheets\Employee Time Report for - " & fn & ", " & Ln & ", " & sdt & " to " & edt & ".xls"
    t = Len(Dir(gg))
    If t = 0 Then
    GoTo keepgoing1
    Else
    t = MsgBox("File already exists, Delete file and continue ?.", vbYesNo, "")
    If t = vbYes Then
    Kill gg
    Else
    Exit Sub
    End If
    End If
    keepgoing1:
    On Error Resume Next
    Me.barcode = bc
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "print time sheets for selected employees", gg, True
    On Error GoTo 0
    Set Excel_Workbook = GetObject(gg)
    Set Excel_Application = Excel_Workbook.Parent
    Excel_Application.WindowState = xlMaximized
    Excel_Application.Visible = True
    Excel_Workbook.Windows(1).Visible = True
    Excel_Workbook.Worksheets(1).Name = "Employee Time Report"
    Set Current_Worksheet = Excel_Workbook.Worksheets("Employee Time Report")
    Excel_Workbook.Worksheets("Employee Time Report").Select
    Excel_Workbook.Worksheets("Employee Time Report").Tab.ColorIndex = 37
    Set Current_Worksheet = Excel_Workbook.Worksheets("Employee Time Report")
    Excel_Workbook.Worksheets("Employee Time Report").Select
    Excel_Application.FormulaBarHeight = 1
    Current_Worksheet.Cells.Select
    With Selection
    Current_Worksheet.Cells.HorizontalAlignment = xlRight
    Current_Worksheet.Cells.Font.Name = "Times New Roman"
    End With

    ...continues with more code but the issue is above in red, if i delete that line it fails on the next line.

    1000 ways to skin a cat, allways looking for another one...
    Use MDB format for sample post. If your issue is fixed, mark the thread solved.
    Click on the star below if this has helped.

  2. #2
    trevor40's Avatar
    trevor40 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    407
    I have progressed a bit, it seems that after doing the transferspreadsheet the files does exist, and seems to have the correct data in it.
    then you see nothing until this line runs
    Excel_Application.WindowState = xlMaximized then you see the excel app but no sheet loaded, and naturally the code after this will fail due to no sheet in the excel app.
    So for some reason the code works fine on windows 7 access 2010, but on windows 8.1 the file is not being opened after being created.

    any help would be great

    1000 ways to skin a cat, allways looking for another one...
    Use MDB format for sample post. If your issue is fixed, mark the thread solved.
    Click on the star below if this has helped.

  3. #3
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Try replacing it with this:
    Code:
    Excel_Workbook.Activate

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I see a few things that should be fixed.

    You have "Dim rst As DAO.Recordset", but then use "rs"
    Code:
        Dim rst As DAO.Recordset
    <snip>
    <snip>
        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")
    
    <snip>
    You probably don't have "Option Explicit" as the 2nd line in your module because you have a lot of undeclared variables: sdt, edt, fn, Ln, ns, ne, bc, t, rs (as noted above)

    Shouldn't use spaces, punctuation or special characters (except the underscore) in object names.
    I am surprised that you don't get an error using a comma in a file name. Underscores would be better than commas.
    Code:
                gg = "C:\aaa\timesheets\EmployeeTimeReport_for_" & fn  & "_" &  Ln & "_" & sdt & "_to_" &  edt & ".xls"

    The code could be re-written to eliminate the Goto:
    Code:
               <snip>
                t = Len(Dir(gg))
                If t = 0 Then
                    GoTo keepgoing1
                Else
                    t = MsgBox("File already exists, Delete file and continue ?.", vbYesNo, "")
                    If t = vbYes Then
                        Kill gg
                    Else
                        Exit Sub
                    End If
                End If
    keepgoing1:
                On Error Resume Next
                Me.barcode = bc
    
               <snip>
    Code:
              <snip>
                t = Len(Dir(gg))
                If t <> 0 Then
                    t = MsgBox("File already exists, Delete file and continue ?.", vbYesNo, "")
                    If t = vbYes Then
                        Kill gg
                    Else
                        Exit Sub
                    End If
                End If
    
                On Error Resume Next
                Me.barcode = bc
                <snip>

    Have you looked at Ken Snell's site at http://www.accessmvp.com/kdsnell/EXCEL_Export.htm
    Lots of examples of writing to Excel sheets......

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Having major issues with Access 2007
    By dRocK in forum Access
    Replies: 2
    Last Post: 05-22-2014, 08:11 AM
  2. Detect which version of Windows is running
    By GraeagleBill in forum Programming
    Replies: 2
    Last Post: 12-31-2012, 05:07 PM
  3. Replies: 12
    Last Post: 02-09-2012, 02:27 PM
  4. Macro having issues when run on Windows Scheduler
    By faeren in forum Programming
    Replies: 4
    Last Post: 09-15-2011, 09:59 AM
  5. Replies: 2
    Last Post: 05-19-2010, 02:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums