D
dj100
my platform is office 2003 (english) Sp1.
Usuallly I use Ado and "select * from [sheet1$]" to deal with some database
in excel spreadsheet. but I found the vba program will be stopped if one cell
of some text field include a lot of characters. I have tested it. I found if
the number of all characters in one cell is less than or equal to 911, it is
okay. But if the number is bigger than 911, then vba will be stoped.
show:
run-time error : -2147467259 (80004005)
Method copyfromrecordset of object range failed
my code is :
Sub inputdata()
Dim x As Object
Dim yy As Object
Dim sql As String
Dim mysheet As Object
Set x = CreateObject("ADODB.Connection")
x.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data
Source=" & ThisWorkbook.FullName
Set mysheet = Sheet2
sql = " select * from [sheet1$] "
Set yy = x.Execute(sql)
mysheet.Cells(1, 1).CopyFromRecordset yy
Set x = Nothing
Set yy = Nothing
End Sub
I do not how to upload my excel spreadsheet in this forum. if someone knows,
please help me. You could leave a email, I could send the spreadsheet to you,
you can test.
by the way, to my surprise, my chinese friend, who use officexp, or office
2003 (chinese) can run the program in excel no problem. the result can
correct even the number of characters in one cell increases to 9000.
could you give me some feedback. thank you.
Usuallly I use Ado and "select * from [sheet1$]" to deal with some database
in excel spreadsheet. but I found the vba program will be stopped if one cell
of some text field include a lot of characters. I have tested it. I found if
the number of all characters in one cell is less than or equal to 911, it is
okay. But if the number is bigger than 911, then vba will be stoped.
show:
run-time error : -2147467259 (80004005)
Method copyfromrecordset of object range failed
my code is :
Sub inputdata()
Dim x As Object
Dim yy As Object
Dim sql As String
Dim mysheet As Object
Set x = CreateObject("ADODB.Connection")
x.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data
Source=" & ThisWorkbook.FullName
Set mysheet = Sheet2
sql = " select * from [sheet1$] "
Set yy = x.Execute(sql)
mysheet.Cells(1, 1).CopyFromRecordset yy
Set x = Nothing
Set yy = Nothing
End Sub
I do not how to upload my excel spreadsheet in this forum. if someone knows,
please help me. You could leave a email, I could send the spreadsheet to you,
you can test.
by the way, to my surprise, my chinese friend, who use officexp, or office
2003 (chinese) can run the program in excel no problem. the result can
correct even the number of characters in one cell increases to 9000.
could you give me some feedback. thank you.