我做的将excel中数据导入到access
代码如下:
Dim db As dao.Database
Dim rs As dao.Recordset
Set db = OpenDatabase("C:\mb\FRDMb.xls", True, False, "Excel 8.0")
'Set rs = db.OpenRecordset("select * from [pool$]")
db.Execute ("Insert into [;database=C:\data\dbData_20060710.mdb].data select * FROM [poolall$] Where Company_ID = 'nokia'
")现在有个问题
字段:Emp_Code 有的是数字,有的是数字(如123)有的是文本如(FA04301)nokia的Emp_Code是文本形式的,我在执行上面的语句后Emp_Code字段在数据库中是空的其他字段没有问题
大概是什么原因呢?
代码如下:
Dim db As dao.Database
Dim rs As dao.Recordset
Set db = OpenDatabase("C:\mb\FRDMb.xls", True, False, "Excel 8.0")
'Set rs = db.OpenRecordset("select * from [pool$]")
db.Execute ("Insert into [;database=C:\data\dbData_20060710.mdb].data select * FROM [poolall$] Where Company_ID = 'nokia'
")现在有个问题
字段:Emp_Code 有的是数字,有的是数字(如123)有的是文本如(FA04301)nokia的Emp_Code是文本形式的,我在执行上面的语句后Emp_Code字段在数据库中是空的其他字段没有问题
大概是什么原因呢?
解决方案 »
- VB对数组元素有限制?一个超多元素的数组无法在VB编辑器里写入,咋办?
- 跪求帮助!!!!高手请进!!!!
- 如何在RichTextBox中取出一段被选中的字符串放入一个变量中?急急100分相送
- 关于操作word的两个问题,急!!!!!
- 微软就要不再支持VB6了,这意味着?
- 局域网中的打字软件
- winamp3.0的那种效果是如何实现的?
- vb中往access创建新表,如何使表中字段的“必填字段”属性为“否“?
- 请问谁有VB写的五子棋源程序?急
- DefineDosDevice虚拟的磁盘如何卸载?
- 如何将label控件“托拽”到屏幕中的文本框,释放鼠标后将label内容填入到文本框中?
- 如何屏蔽Win键、DEL+ATL+CTL、CTL+ESC(XP系统)
Dim db As dao.Database
Dim rs As dao.Recordset
Set db = OpenDatabase("data\dbData_20060710.mdb")db.Execute "Insert into data select * FROM [poolall$] In ""C:\mb\FRDMb.xls"" ""Excel 8.0;"" Where Company_ID = 'nokia'"
When connecting to an Excel Spreadsheet using the DAO OpenRecordset method, some values in an Excel column may be returned as Null when the underlying value is not a Null. This typically occurs when numeric and text datatypes are intermixed within the same Excel column.
Back to the top CAUSE
This problem is caused by a limitation of the Excel ISAM driver in that once it determines the datatype of an Excel column, it will return a Null for any value that is not of the datatype the ISAM driver has defaulted to for that Excel column. The Excel ISAM driver determines the datatype of an Excel column by examining the actual values in the first few rows and then chooses a datatype that represents the majority of the values in its sampling.
Back to the top RESOLUTION
There are two workarounds for this behavior:
1. Insure that the data in Excel is entered as text. Just reformatting the Excel column to Text will not accomplish this. You must re-enter the existing values after reformatting the Excel column. In Excel, you can use F5 to re-enter existing values in the selected cell.
2. You can add the option IMEX=1; to the Excel connect string in the OpenDatabase method. For example: Set Db = OpenDatabase("C:\Temp\Book1.xls", _
False, True, "Excel 8.0; HDR=NO; IMEX=1;")
NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric. You must be careful that IMEX=1 not be used indiscriminately. This is IMPORT mode, so the results may be unpredictable if you try to do appends or updates of data in this mode. The possible settings of IMEX are: 0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)
The registry key where the settings described above are located is: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel
Dim xls As Object
Set xls = CreateObject("excel.application")
xls.Workbooks.Open "c:\test.xls"
xls.Visible = True
Dim i As Long
Dim j As Long
With xls.Workbooks(1).Sheets("Sheet1")
For i = 1 To .UsedRange.Rows.Count
For j = 1 To .UsedRange.Columns.Count
.Cells(i, j) = "'" & .Cells(i, j)
Next
Next
End With
xls.DisplayAlerts = False
xls.ActiveWorkbook.Save
xls.Quit
Set xls = Nothing
End Sub......
您好,字段:Emp_Code 存储的是员工编号,有些公司就是按照数字来编号的
有些公司是按照公司规则进行编号的(就是文本)我不能改变这些信息阿
我在excel中加了一列,然后用公式=CONCATENATE("'",J5)
,然后将结果选择性粘贴(选择数值),但是有个问题单元格里显示的是:“'6”
这样导入到数据库中存的是“'6”如何解决呢?