各位大虾好!小弟最近刚接入VB的开发。
现遇到如下问题,请尽快协助,不胜感谢!
现欲实现EXCEL数据导入ACCESS,但遇到EXCEL表中有些数据是以 空格 或 单引号开头 开头,如:'88899," ieplore"等等。
所以需要在导入ACCESS之前对EXCEL作预处理,也就是先修正EXCEL中所有以 空格或单引号开头的数据,删除前面的空格或单引号。或是将前面有空格或单引号都替换为空。
谢谢各位大虾!
现遇到如下问题,请尽快协助,不胜感谢!
现欲实现EXCEL数据导入ACCESS,但遇到EXCEL表中有些数据是以 空格 或 单引号开头 开头,如:'88899," ieplore"等等。
所以需要在导入ACCESS之前对EXCEL作预处理,也就是先修正EXCEL中所有以 空格或单引号开头的数据,删除前面的空格或单引号。或是将前面有空格或单引号都替换为空。
谢谢各位大虾!
Dim a As String, b As String
a = " 'adfsdf'asdf'sdf"
b = Replace(a, "'", "") '将变量A中的所有'替换成空
b = Replace(a, " ", "") '将变量A中的所有空格替换成空
End Sub
另能否给一个完整的程序,将它做成一个模块,谢谢!
如何在导入前检查EXCEL一列中的数据是否为统一类型,
若不统一,是否可以用程序修改为统一类型
STRSQL = "INSERT INTO TMA001(ITEM_CD) VALUES(Trim(xlsheet.Cells(i, j).Value);"
End If
If Asc(Left(xlsheet.Cells(i, j).Value, 1)) = 39 Then '单引号
STRSQL = "INSERT INTO TMA001(ITEM_CD) VALUES(MID(xlsheet.Cells(i, j).Value, 2,LEN(xlsheet.Cells(i, j).Value)));"
End If
CellsValue = Trim(CellsValue)
If Asc(Left(CellsValue, 1)) = 39 Then
CHANGE = Mid(CellsValue, 2, Len(CellsValue))
Else
CHANGE = CellsValue
End If
这个模块,应该可以的
<<vb表导入Excel>> 得引用Microsoft Excel 9.0 Object Library
用 记录集就行 自定义过程如下
Public Sub ProCopyAdoRsToExcel(SAdoRsTmp As ADODB.Recordset, SheetName As String) Dim appExcel As Excel.Application '通用EXCEL对象
Dim wbExcel As Excel.Workbook '指定EXCEL对象
Dim TempSheet As Excel.Worksheet '工作单对象
Dim TempRange As Excel.Range '限制行 Dim LongRow As Long, LongCol As Long '循环变量 If Not (SAdoRsTmp.EOF Or SAdoRsTmp.BOF) Then
SAdoRsTmp.MoveFirst: SAdoRsTmp.MoveFirst
Set appExcel = CreateObject("excel.application")
Set wbExcel = appExcel.Workbooks.Open("d:\tj.xls") '打开文件
Set TempSheet = appExcel.Worksheets(SheetName)
TempSheet.Cells.Clear '清空现有数据
LongRow = 0
Set TempRange = TempSheet.Rows(LongRow + 1) '标题
Do While LongCol <= SAdoRsTmp.Fields.Count - 1
TempRange.Cells(LongRow + 1, LongCol + 1) = CStr(SAdoRsTmp.Fields(LongCol).Name)
LongCol = LongCol + 1
Loop '内容
LongRow = 1
Do While LongRow <= SAdoRsTmp.RecordCount
LongCol = 0
Do While LongCol <= SAdoRsTmp.Fields.Count - 1
If Not IsNull(SAdoRsTmp.Fields(LongCol)) Then
TempRange.Cells(LongRow + 1, LongCol + 1) = CStr(SAdoRsTmp.Fields(LongCol))
End If
LongCol = LongCol + 1
Loop
LongRow = LongRow + 1
SAdoRsTmp.MoveNext
Loop Set TempSheet = Nothing '关闭对象
wbExcel.Save
wbExcel.Close
Set wbExcel = Nothing
Set appExcel = Nothing
End IfEnd Sub
Dim objFileSystem As Object
Dim objExcelText As Object
Dim strTableString As String, i As Integer, strFileName As String
Dim pubConn As New ADODB.Connection
Dim rsTable As New ADODB.Recordset
Dim strConn As String
Dim strSQL As String strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=develop; password=12345;Data Source=ServerNmae"
pubConn.Open strConn
rsTable.CursorLocation = adUseClient
strSQL = "select top 10 * from gate_register"
rsTable.Open strSQL, pubConn, adOpenDynamic, adLockOptimistic
For i = 0 To rsTable.Fields.Count - 1
strTableString = strTableString & rsTable.Fields(i).Name & Chr(9) '獲取字段名
Next
strTableString = strTableString & rsTable.GetString '字段名+數據庫的記錄
cmDialog.CancelError = False
cmDialog.FileName = "FileName" '默認生成的文件名
cmDialog.DialogTitle = "Save Export File"
cmDialog.Filter = "Excel (*.xls)|*.xls|文本文件(*.DBF)|*.DBF|檔案文件(*.doc)|*.doc|所有文件(*.*)|*.*"
cmDialog.DefaultExt = "*.xls"
cmDialog.ShowSave
strFileName = cmDialog.FileName
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set objExcelText = objFileSystem.createtextfile(strFileName, True)
objExcelText.writeline (strTableString)
objExcelText.Close
Set objFileSystem = Nothing
End Sub
或者是有一个单元格一个单元格地导入ACCESS表的内容的模块。谢谢!
哪位大哥斑斑忙
最好是模块化的。以便于其他过程来调用。谢谢各位关注,在线等!!!自己顶一下!!!!!!!