reading range n storing data in Access

[rana.sk]

hi All,

I have a non- contiguos range of around 10 columns and 30 rows.
i need to move the entire data into an Access database.
Can nybody plz provide any help on this .

[antoka05]

You can try to use next code but you need to activare Ms DAO:

open VB Editor (Alt + F11)
choose menu Tools, then Reference and the, in the list, choose Microsoft DAO 3.xx Object Library (where xx is 51 or 6 or other)

then paste this code:

Sub ExportToAccess()
   Dim db As Database
   Dim rs As Recordset
   
   Dim dbPath As String
   Dim dbName As String

   dbPath = "C:\...\" 'path of the mdb file
   dbName = "<db name>.mdb" 
   
   If Right(dbPath, 1) <> "\" Then
      dbPath = dbPath & "\"
   End If
   
   'open database
   Set db = DBEngine.Workspaces(0).OpenDatabase(dbPath & dbName)
   
   'delete records in table
   db.Execute "delete from <table name>" 'not include < and > chars
   
   Set rs = db.OpenRecordset("select * from <table name>") 'not include < and > chars
   
   'storing data in access
   columnsToStore = "A -C -D -E -G -I -K -AA-AC-AF" 'each column have 2    bytes and one to be free 
   'The columns you specify will be put, in the order, in db table 
   'column A in the first field of the table, column C in the second 'field... 
   'column AF in the last field

   'rows to store in db 
   For r = 2 To 30
      'add new blnk record
      rs.AddNew
      
      'updating fields
      For c = 1 To rs.Fields.Count
         columnLetter = RTrim(Mid(columnsToStore, 3 * c - 2, 1))
         columnNumber = Columns(columnLetter).Column
         rs(c - 1) = ActiveSheet.Cells(r, columnNumber)
      Next
      
      'confirm update
      rs.Update
   Next
   
   rs.Close
   db.Close
   Set db = Nothing
End Sub

I hope it's what you need.

Regards,
Antonio

[rana.sk]

Thanks a ton ..!!!

Regards,
rana