Solved

From Excel to Access VBA

Posted on 2014-10-28
3
313 Views
Last Modified: 2014-10-29
Hello,

I have a sheet in Excel "RawData" and I have a Access DB with the table "RawData"
I need to write the VBA code (in my Excel macro file) that will move data from my Excel "RawData"
and add it to my Access "RawData". Without opening the Access.

This is what I've wrote so far (getting run time error 3265, Item not found in this collection)

Option Explicit
Sub DB_Export_ToAccess()

  Dim oDAO As DAO.DBEngine
  Dim oDB As DAO.Database
  Dim oRS As DAO.Recordset
 
  Dim lngRow As Long
  Dim intCol As Integer
 
  pubStrDBPath = "\\**************\XcelAccess.mdb"
 
  'Check that DB file exists
  If Dir(pubStrDBPath) = "" Then
    MsgBox "DB Access file " & pubStrDBPath & " Not found."
    Exit Sub
  End If
 
  'Open DB
  Set oDAO = New DAO.DBEngine
  Set oDB = oDAO.OpenDatabase(pubStrDBPath)
  Set oRS = oDB.OpenRecordset("RawData", dbOpenDynaset)
 
  'Populate record set
  For lngRow = 2 To Worksheets("RawData").UsedRange.Rows.Count
    oRS.AddNew
    For intCol = 1 To Worksheets("RawData").UsedRange.Columns.Count
      oRS.Fields(intCol) = Worksheets("RawData").Cells(lngRow, intCol)
    Next intCol
   
    oRS.Update
  Next lngRow
 
  oDB.Close
End Sub
0
Comment
Question by:CABRLU63
  • 2
3 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40410089
Field index is zero based, thus:

      oRS.Fields(intCol - 1) = Worksheets("RawData").Cells(lngRow, intCol)

/gustav
0
 

Author Closing Comment

by:CABRLU63
ID: 40410552
Gustav, this works like gold now, thanks!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40410568
You are welcome!

/gustav
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Unrecognized Database Format 8 91
Sub Reports 8 21
addressing a subform frim a subform inside it 8 22
Access coding 2 0
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now