|
 |
access thread: Method 'Range' of object '_Global' failed
Message #1 by RPCABQ@a... on Tue, 10 Jul 2001 01:35:13 EDT
|
|
(This message is somewhat illegible, this is what happens to MIME
messages on their way to us sometimes. Please send plain text only if
possible - from AOL this means using their webmail service.
- moderator)
Run Time Error 1004
Method =E2=80=98Range=E2=80=99 of object =E2=80=98_Global=E2=80=99 failed
A form containing 11 radio buttons is used to determine which Excel
spreadsheet to open =E2=80=93 insert data=C2=A0=E2=80=93 format columns =E2
=80=93 sort data =E2=80=93 and close
spreadsheet.
Problem:=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A01s
t radio button selected=C2=A0 -- works correctly
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 2nd radio butto
n selected=C2=A0 -- error 1004 above is
broadcast =E2=80=93 then end subroutine from error message
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 3rd radio butto
n selected=C2=A0 -- works correctly
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 4th radio butto
n selected=C2=A0 -- error 1004 above is
broadcast =E2=80=93 then end subroutine from error message
and so on where every other button works and every other doesn't work. If th
e
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 =E2=80=98Range=E2=80=99 of object=E2=80=98_Global=E2=80=99 failed
=C2=A0
A form containing 11 radio buttons is used to determine which Excel
spreadsheet to open =E2=80=93 insert data =E2=80=93 format columns =E2=80
=93 sort data =E2=80=93 and close
spreadsheet.
=C2=A0
Problem: 1st radio button selected -- works correctly
2nd radio button selected -- error 1004 above is
broadcast =E2=80=93 then end subroutine from error message
3rd radio button selected -- works correctly
4th radio button selected -- error 1004 above is
broadcast =E2=80=93 then end subroutine from error message
and so on where every other button works and every other doesn't work. If th
e
2nd button is the 1st selected then the 3rd button has an error and the 4th
works and so on.
=C2=A0
Below is the problem code
=C2=A0
xlsheet.Rows(=E2=80=9C4:500=E2=80=9D).SortKey1:=3DRange(=E2=80=9CA4=E2=80
=9D),Order1:=3DxlAscending,
Header:=3DxlGuess, _
OrderCustom:=3D1,MatchCase:=3DFalse, Orientation:=3DxlTopToBottom
=C2=A0
Below is the code used to open =E2=80=93 insert =E2=80=93 format =E2=80=93 s
ort data =E2=80=93 and close
=C2=A0
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
=C2=A0
=C2=A0
strRst =3D =E2=80=9CtblAppendLoc=E2=80=9D
=C2=A0
=C2=A0
Set rst2 =3Ddb.OpenRecordset(strRst)
=C2=A0
=C2=A0
lblStatus.Caption =3D =E2=80=9CExporting Data to ExcelSpreadsheet=E2=80=9D
Me.Repaint
=C2=A0
=C2=A0
Set xl =3D CreateObject(=E2=80=9Cexcel.application=E2=80=9D)
Set xlwk =3Dxl.Workbooks.Open(=E2=80=9CG:\Health &Safety\=E2=80=9D & strFile
)
Set xlsheet =3Dxlwk.worksheets(=E2=80=9Csheet1=E2=80=9D)
xlsheet.Rows(=E2=80=9C4:500=E2=80=9D).Delete =E2=80=98 delete
old data from
spreadsheet
xlsheet.Range(=E2=80=9CA1=E2=80=9D).Select
=C2=A0
xlsheet.Range(=E2=80=9CA4=E2=80=9D).CopyFromRecordsetrst2 =20
=E2=80=98 insert new data
to spreadsheet
=C2=A0
xlsheet.Columns(=E2=80=9CE=E2=80=9D).NumberFormat=3D=E2=80=9Cmm/dd/yy=E2=80
=9D =E2=80=98 set format
for date columns
xlsheet.Columns(=E2=80=9CG:K=E2=80=9D).NumberFormat=3D=E2=80=9Cmm/dd/yy=E2
=80=9D
xlsheet.Columns(=E2=80=9CM=E2=80=9D).NumberFormat=3D=E2=80=9Cmm/dd/yy=E2=80
=9D
xlsheet.Columns(=E2=80=9CS=E2=80=9D).NumberFormat=3D=E2=80=9Cmm/dd/yy=E2=80
=9D
xlsheet.Columns(=E2=80=9CW=E2=80=9D).NumberFormat=3D=E2=80=9Cmm/dd/yy=E2=80
=9D
xlsheet.Columns(=E2=80=9CY:AB=E2=80=9D).NumberFormat=3D=E2=80=9Cmm/dd/yy=E2
=80=9D
xlsheet.Columns(=E2=80=9CAD=E2=80=9D).NumberFormat=3D=E2=80=9Cmm/dd/yy=E2
=80=9D
xlsheet.Columns(=E2=80=9CAF=E2=80=9D).NumberFormat=3D=E2=80=9Cmm/dd/yy=E2
=80=9D
xlsheet.Columns(=E2=80=9CAH:AI=E2=80=9D).NumberFormat=3D=E2=80=9Cmm/dd/yy
=E2=80=9D
xlsheet.Columns(=E2=80=9CAK:AL=E2=80=9D).NumberFormat=3D=E2=80=9Cmm/dd/yy
=E2=80=9D
xlsheet.Columns(=E2=80=9CAN:AO=E2=80=9D).NumberFormat=3D=E2=80=9Cmm/dd/yy
=E2=80=9D
=C2=A0
=E2=80=98 sort excel data
xlsheet.Rows(=E2=80=9C4:500=E2=80=9D).SortKey1:=3DRange(=E2=80=9CA4=E2=80
=9D),Order1:=3DxlAscending,
Header:=3DxlGuess, _
OrderCustom:=3D1,MatchCase:=3DFalse, Orientation:=3DxlTopToBottom
=C2=A0
xl.Range(=E2=80=9CA4=E2=80=9D).Select
=C2=A0
xlwk.Close
xl.Quit
lblStatus.Caption =3D =E2=80=9CExport Complete=E2=80=9D
Me.Repaint
Message #2 by RPCABQ@a... on Thu, 19 Jul 2001 00:49:34 EDT
|
|
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
Message #3 by Walt Morgan <wmorgan@s...> on Thu, 19 Jul 2001 06:50:49 -0500
|
|
Original:
xlsheet.Range("A4").CopyFromRecordsetrst2 ' insert new data to spreadsheet
^^^^^^^
This Should Work:
xlsheet.Range("A4").CopyFromRecordset rst2 ' insert new data to spreadsheet
^^^^^^^^
|
|
|
View Cart
