-Ïîèñê ïî äíåâíèêó

Ïîèñê ñîîáùåíèé â rss_sql_ru_access_programming

 -Ïîäïèñêà ïî e-mail

 

 -Ïîñòîÿííûå ÷èòàòåëè

 -Ñòàòèñòèêà

Ñòàòèñòèêà LiveInternet.ru: ïîêàçàíî êîëè÷åñòâî õèòîâ è ïîñåòèòåëåé
Ñîçäàí: 16.03.2006
Çàïèñåé:
Êîììåíòàðèåâ:
Íàïèñàíî: 4


VBA; Ëèøíèå ïðîáåëû ïðè èìïîðòå

Ïÿòíèöà, 02 Èþíÿ 2017 ã. 14:31 + â öèòàòíèê
Äîáðîãî âðåìåíè ñóòîê

Äëÿ èìïîðòà äàííûõ èç Excell â Access èñïîëüçóþ ïðîöåäóðó:
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


Ïðè èìïîðòå äàííûõ èç ëèñòà excel â ïîëå òàáëèöû access ïîÿâëÿþòñÿ ëèøíèå ïðîáåëû. Â ôàéëå excel, òåì íå ìåíåå, ýòèõ ïðîáåëîâ íåò.
ß ïðîáîâàë è
![OBSN] = RTrim(wrksheet.Cells(i, "B"))

È
![OBSN] = replace(wrksheet.Cells(i, "B"), " ", "")

â ò.÷. è ôóíêöèþ zamena, ïðåäîñòàâëåííóþ ìíå ðàíåå ïîëüçîâàòåëåì ÏÅÍÑÈÎÍÅÐÊÀ. Âñ¸ ðàâíî èìïîðòèðóåò ñ ïðîáåëàìè.
Êîãäà ÿ ñìåíèë â îäíîì èç ïîëåé òàáëèöû òèï ñ òåêñòîâîãî íà ìåìî, äàííûå â ýòî ïîëå çàíåñëèñü áåç ïðîáåëîâ, êàê ïîëîæåíî. Íî ýòî íå âûõîä, ò.ê. îò ìåíÿ òðåáóþòñÿ òàáëèöû ñ ïîëÿìè îïðåäåë¸ííîãî òèïà. Íî è áåç ýòîãî ñ÷èòàþ íóæíûì ïîíÿòü ïðîáëåìó.
Ïîìîãèòå ðàçîáðàòüñÿ

http://www.sql.ru/forum/1261939/vba-lishnie-probely-pri-importe


 

Äîáàâèòü êîììåíòàðèé:
Òåêñò êîììåíòàðèÿ: ñìàéëèêè

Ïðîâåðêà îðôîãðàôèè: (íàéòè îøèáêè)

Ïðèêðåïèòü êàðòèíêó:

 Ïåðåâîäèòü URL â ññûëêó
 Ïîäïèñàòüñÿ íà êîììåíòàðèè
 Ïîäïèñàòü êàðòèíêó