Extracting data from excel spreadsheet to update records in Access Database

Neil_Pattison

Registered User.
Local time
Today, 17:34
Joined
Aug 24, 2005
Messages
73
Hi guys, can someone please point me in the right direction

I'm sure there will be a way, but I cant think of it, that you can extract data from an excel spreadsheet and update/append records in an MS Access database using VBA code.

Any help would be gretaly appreciated. Thanking you in advance
 
You can link an Excel spreadsheet into an access database as a linked table. Then you can access it as you would any other table and use the info in queries etc.

Just use the the link tables option in the New table dialogue.
 
Thanks for your reply.

The problem I've got is that it is a number of different spreadsheets and new ones will continue to come in daily so I can't just use a link
 
You can also read the spreadsheets by using code similar to this which I use for reading in daily reports.

Code:
Sub GetData(Filename As String)
    Dim Datadate As String, wksBook As Excel.Workbook, srcBook As Excel.Workbook, wksSheet As Excel.Worksheet
    Dim NewString As String, Counter As Integer, srcFile As String, objExcel As Excel.Application
    Dim TeamManager As String, CellRef As String, WorkRef As String, TmRef As String, Countera As Integer
    Dim VMMpage As String, strQuery As String
    
    srcFile = "H:\filedirectory\" &  Filename
   ' MsgBox (srcFile)
    Workbooks.Open (srcFile)
    
    Set srcBook = ActiveWorkbook
    Sheets(ActiveSheet.Name).Name = "Sessiondata"
    Datadate = Mid(FileDate, 5, 2) & "/" & Mid(FileDate, 7, 2) & "/" & Left(FileDate, 4)

Hope this helps
 

Users who are viewing this thread

Back
Top Bottom