VBA; |
Public Sub Ex2Acc() ' ' Dim sheet As Excel.Worksheet Dim book As Excel.Workbook Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim rstb As String Dim rstEr As DAO.Recordset Dim appXl As Excel.Application Dim wrksheet As Excel.Worksheet Dim i As Long rstb = Forms![Form1].[13].Value Set appXl = CreateObject("Excel.Application") Set book = appXl.Workbooks.Open(Forms![Form1].[3].Value) Set dbs = CurrentDb Set rst = CurrentDb.OpenRecordset(rstb) Set wrksheet = book.Sheets(1) With book.Sheets(1) For i = 5 To 100 If InStr(1, wrksheet.Cells(i, "H").Value, "ns") > 0 Then With rst .AddNew ' On Error Resume Next ![OBSN] = RTrim(wrksheet.Cells(i, "B")) ![NAIM] = RTrim(wrksheet.Cells(i, "C")) ![ED_IZM] = RTrim(wrksheet.Cells(i, "D")) ![BRUTTO] = wrksheet.Cells(i, "E") ![C_BASE] = zamena(wrksheet.Cells(i, "F")) ![CLASS_GR] = zamena(wrksheet.Cells(i, "G")) ![COD_UZ] = zamena(wrksheet.Cells(i, "H")) ![C_OPT] = zamena(wrksheet.Cells(i, "I")) ![C_SMET] = zamena(wrksheet.Cells(i, "J")) ![IND] = zamena(wrksheet.Cells(i, "K")) .Update End With End If Next End With rst.Close: Set rst = Nothing dbs.Close: Set rst = Nothing book.Close: Set book = Nothing appXl.Quit: Set appXl = Nothing MsgBox "" ' Exit Sub ' CurrentDb.Execute "CREATE TABLE Errors(RowNumbers CHAR(15))" ' Set rstEr = CurrentDb.OpenRecordset(Errors) ' With wrksheet ' With rstEr ' .AddNew ' ![RowNumbers] = wrksheet.Cells(i, "A") ' .Update ' End With ' End With End Sub
![OBSN] = RTrim(wrksheet.Cells(i, "B"))
![OBSN] = replace(wrksheet.Cells(i, "B"), " ", "")
http://www.sql.ru/forum/1261939/vba-lishnie-probely-pri-importe