Hopefully this is legible from AOL



(Beware exrra line wraps - moderator)



Run Time Error 1004

Method ‘Range’ of object ‘_Global’ failed



A form containing 11 radio buttons is used to determine which 

Excel spreadsheet to open – insert data – format 

columns – sort data – and close spreadsheet. 



Problem:           1st radio button selected  -- works correctly

                        2nd radio button selected  -- error 1004 

above is broadcast – then end subroutine from error message

                        3rd radio button selected  -- works correctly

                        4th radio button selected  -- error 1004 

above is broadcast – then end subroutine from error message

and so on where every other button works and every other doesn't 

work. If the 2nd button is the 1st selected then the 3rd button 

has an error and the 4th works and so on.



Below is the problem code



Run Time Error 1004

Method ‘Range’ of object‘_Global’ failed

 

A form containing 11 radio buttons is used to determine which Excel 

spreadsheet to open – insert data – format columns – 

sort data – and close spreadsheet. 

 

Problem:          1st radio button selected  -- works correctly

                      2nd radio button selected  -- error 1004 above 

is broadcast – then end subroutine from error message

                      3rd radio button selected  -- works correctly

                      4th radio button selected  -- error 1004 above 

is broadcast – then  end subroutine from error message

and so on where every other button works and every other doesn't work. 

If the 2nd button is the 1st selected then the 3rd button has an error 

and the 4th works and so on.

 

Below is the problem code

 

xlsheet.Rows(“4:500”).SortKey1:=Range(“A4”),

Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1,MatchCase:=False, Orientation:=xlTopToBottom

 

Below is the code used to open – insert – format – 

sort data – and close

 

Dim xl As Excel.Application

Dim xlwk As Excel.workbook

Dim xlsheet AsExcel.worksheet

Dim xlr, xlc

Dim strRst As String

Dim rst2 As Recordset

 

 

strRst = “tblAppendLoc”

 

 

Set rst2 =db.OpenRecordset(strRst)

 

 

lblStatus.Caption = “Exporting Data to ExcelSpreadsheet”

Me.Repaint

 

 

Set xl = CreateObject(“excel.application”)

Set xlwk =xl.Workbooks.Open(“G:\Health &Safety\” & strFile)

Set xlsheet =xlwk.worksheets(“sheet1”)

  

  xlsheet.Rows(“4:500”).Delete             ‘ delete 

old data from spreadsheet

  xlsheet.Range(“A1”).Select

 

xlsheet.Range(“A4”).CopyFromRecordsetrst2  

               ‘ insert new data to spreadsheet

 

xlsheet.Columns(“E”).NumberFormat=“mm/dd/yy”

                  ‘ set format for date columns

xlsheet.Columns(“G:K”).NumberFormat=“mm/dd/yy”

xlsheet.Columns(“M”).NumberFormat=“mm/dd/yy”

xlsheet.Columns(“S”).NumberFormat=“mm/dd/yy”

xlsheet.Columns(“W”).NumberFormat=“mm/dd/yy”

xlsheet.Columns(“Y:AB”).NumberFormat=“mm/dd/yy”

xlsheet.Columns(“AD”).NumberFormat=“mm/dd/yy”

xlsheet.Columns(“AF”).NumberFormat=“mm/dd/yy”

xlsheet.Columns(“AH:AI”).NumberFormat=“mm/dd/yy”

xlsheet.Columns(“AK:AL”).NumberFormat=“mm/dd/yy”

xlsheet.Columns(“AN:AO”).NumberFormat=“mm/dd/yy”

 

          ‘ sort excel data

xlsheet.Rows(“4:500”).SortKey1:=Range(“A4”),

Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1,MatchCase:=False, Orientation:=xlTopToBottom

 

xl.Range(“A4”).Select

 

xlwk.Close

xl.Quit

lblStatus.Caption = “Export Complete”

Me.Repaint