Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

Thread Tools Rate Thread Display Modes
Old 08-07-2012, 05:11 PM   #1
Newly Registered User
Join Date: Aug 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
mythandier is on a distinguished road
Question [Help]VBA Macro To Import Specific Cells from Excel to Access

Hello all,

I am currently working on writing a VB Macro that imports data from over 1000 Excel files into a single Access DB.

Right now, I'm using a DoCmd.TransferSpreadsheet Loop which works but due to how the Excel worksheets are formatted, it just isn't usable.

So what I need to do is the same type of function (can extract data from every .xls file in a specified file path) but ONLY pulls specific data from cells (ie, A8, A15, H29...etc). I need to pull the data from these cells into specific fields within Access so that: Field1 contains all data from all the A8 cells in all the worksheets, Field2 contains all data from all the A15 cells in all the worksheets...and so on.

The worksheet from each Excel file is not the first worksheet in the workbook so it has to be identified in the process.

If anyone can help I would be greatly appreciative.

I've included my current macro below. Please ignore all the From 1 To 1 nonsense since I am including that so that at a later date I may add additional worksheets or tables.


Sub AutomatedDataPull()

Dim strPathFile As String, strFile As String, strPath As String
Dim blnHasFieldNames As Boolean
Dim intWorksheets As Integer

' Replace X with the number of worksheets to be imported
' from each EXCEL file

Dim strWorksheets(1 To 1) As String

' Replace X with the number of worksheets to be imported
' from each EXCEL file (this code assumes that each worksheet
' with the same name is being imported into a separate table
' for that specific worksheet name)

Dim strTables(1 To 1) As String

' Replace generic worksheet names with the real worksheet names;
' add / delete code lines so that there is one code line for
' each worksheet that is to be imported from each workbook file

strWorksheets(1) = "Test Program .108.100"

' Replace generic table names with the real table names

strTables(1) = "Tracking"

' Change this next line to True if the first row in EXCEL worksheet
' has field names

blnHasFieldNames = False

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files

strPath = "C:\Access Test Docs\"

' Replace X with the number of worksheets to be imported
' from each EXCEL file

For intWorksheets = 1 To 1

      strFile = Dir(strPath & "*.xls")
      Do While Len(strFile) > 0
            strPathFile = strPath & strFile
            DoCmd.TransferSpreadsheet acImport, _
                  acSpreadsheetTypeExcel9, strTables(intWorksheets), _
                  strPathFile, blnHasFieldNames, _
                  strWorksheets(1) & "$"
            strFile = Dir()

Next intWorksheets

End Sub

mythandier is offline   Reply With Quote

access , excel , import , macro , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
specific cells from Excel to Access iamstupid Excel 5 05-27-2011 02:11 AM
exporting from access to specific cells in excel iamstupid General 0 05-25-2011 08:27 PM
Import Specific Cells from variable sheets into Access Tables. travisdh Modules & VBA 2 01-06-2011 08:39 PM
Import Specific Cells from Excel to Access evocube Modules & VBA 3 11-06-2009 09:05 AM
Exel to access import with specific cells RandRob Macros 3 02-22-2009 02:29 PM

All times are GMT -8. The time now is 10:41 AM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World