Importing specific cells out of various Excel documents into Acces

  • Thread starter Thread starter BTU_needs_assistance_43
  • Start date Start date
B

BTU_needs_assistance_43

I have an Excel program set up to search for recently added Excel documents
out of a certain folder and it works very well at doing that. Then I set up
an Access database to pull the names of those files out of Excel and put them
into a table which imports them into a list on a form. I've found plenty of
code on how to import whole Excel files and how to open Excel from within
Access. However thats not really what I want the program to do.

I want to be able to select the new file names from the list and click a
button and have my Access database import a constant set of cells (it will
always be the specific cells like C3, D3, F3, etc...) from any file I choose
off the list into a table.

That is my main concern but I would also appreciate help setting up my Excel
program to automatically run and update the list of recent files without me
having to open it and do it myself every day. Ill post the VB code from Excel
below, but I'm hitting a complete stone wall on the Access programming so
don't have anything solid. Thanks for any and all help!

Private Sub FileSearch_Click()

With Application.FileSearch
.NewSearch
.LookIn = "X:\File\Sub File\Report File"
.SearchSubFolders = True
.LastModified = msoLastModifiedThisWeek
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
Worksheets("sheet1").Activate
Worksheets("sheet1").Range("A2").Select
For i = 1 To .FoundFiles.Count
ActiveCell.Value = .FoundFiles(i)
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next i
End If
End With

End Sub
 
Your code there is very very close to what I need. "Import Data from A
Specific Worksheet in All EXCEL Files in a single Folder into Separate Tables
via TransferSpreadsheet (VBA)" is just what I need, but is there a way I can
tweak it to pull out 3 specific cells (H57, K57, M57) and put them into my
Table: Cast_Reports in my Access Database?

And is there someway I can incorporate code into it so that instead of
importing EVERY file it finds, it will search for only and add all recently
modified Excel files. I have the basic code for searching for recently added
Excel files, I am just unsure as how to write it into your VB program. This
is what I tried.

Your help is greatly appreciated.

Dim blnHasFieldNames as Boolean
Dim strWorksheet As String, strTable As String
Dim strPath As String, strPathFile As String
Dim strCells (1 to 3) as String

' 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 = "G:\DLDATA\Pit Volumes\East"
..LastModified = msoLastModifiedThisWeek


' Replace worksheetname with the real name of the worksheet that is to be
' imported from each file
strWorksheet = "Cast"


strCells(1) = "(8, 57)"
strCells(2) = "(11, 57)"
strCells(3) = "(13, 57)"

' Import the data from each workbook file in the folder
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
strTable = "tbl_" & Left(strFile, InStrRev(strFile, ".xls") - 1)

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTable, strPathFile, strCells,_
blnHasFieldNames, strWorksheet & "$"

strFile = Dir()
Loop
 
To import from just three, noncontiguous cells, you'll need to use the
Recordset example at my website. Essentially, you'll open the EXCEL file via
Automation, open a recordset to your table, and then read the three EXCEL
cells individually and write them into one new record in the recordset.

Write Data From an EXCEL Worksheet into a Recordset using Automation (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#WriteFileRst


As for only doing this for recently updated files, that probably will
involve using a Scripting.FileSystemObject process in order to read the
DateLastModified property from each file as you identify the filename via
Dir function. If the DateLastModified is "recently updated" per your
standards, then you'd have the code do the read/write process.

Dim fs As Object, f As Object, s As Date
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(PathAndFileNameOfEXCELFile)
s = f.DateLastModified
If s < SomeCutoffDatevalue Then
' continue to do the import
End If

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
Thank you very much. Your code and your website have been extremely helpful
and made my job much easier. I do however have another roadblock that I will
post in another thread if you get a chance to look at it... it is somewhat
more complex.

Thanks again! Your VB and Access savy blows me away.
 
Back
Top