How do I loop through a folder, import only the data from A5:LastRow and kill each file?

I have a folder of formatted macro enabled spreadsheet forms with multiple tabs and data to import and append to an Access 2010 table.  The top 4 rows of the target tab are a header that can be excluded from the import, so what I want to do is grab everything on that one tab from A5 to the bottom, which is different in each file.

I want to loop through the folder, import and kill each file after the import.  Does anyone have a chunk of code for that?
Jay WilliamsOwnerAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
import the excel file to a temp table, then create an append query to append the records from temp table to the  final table.
0
 
Rey Obrero (Capricorn1)Commented:
this is a whole process of file archiving you are asking.

this may help you to start what you want to do

http://allenbrowne.com/ser-59.html
0
 
Rey Obrero (Capricorn1)Commented:
here is the code to search for excel files in a single folder

dim sPath as string, xlFile as string
sPath="C:\myfolder\"

xlFile=dir(spath & "*.xls*")

while xlFile<>""
    debug.print xlfile
    docmd.transferspreadsheet acimport , , "tablename", spath & xlfile, false, "sheetname!A5:Z1000"

    'delete the file

    kill spath & xlfile
   xlFile=dir()
wend
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Jay WilliamsOwnerAuthor Commented:
Thanks, Rey.  This is more appropriate--simple, like me.  Will the Z1000 take me to the last row with entries, or will it only take me to row 1000?
0
 
Rey Obrero (Capricorn1)Commented:
only to row 1000.., i just placed it there :-)
0
 
Jay WilliamsOwnerAuthor Commented:
OK. The data may go farther than that. Another way to go would be to import only rows where column D is populated.  Would that be hard to specify?
0
 
Rey Obrero (Capricorn1)Commented:
well, you need to open the excel file to determine the last row that have data.

i suggest that you just extend the 1000 to get all data.
0
 
Jay WilliamsOwnerAuthor Commented:
Well, F1 does not exist in the destination table. I changed the field names argument to "True", knowing it wouldn't work, and it didn't. The sheet and table structures are the same. How do I fix that?
0
 
Jay WilliamsOwnerAuthor Commented:
Thanks a lot, Rey. Works like a charm.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.