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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-07-2012, 05:11 PM   #1
mythandier
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.

Regards,
CK

Code:
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()
      Loop

Next intWorksheets


End Sub

mythandier is offline   Reply With Quote
Reply

Tags
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 12:57 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


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