Solved

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

Posted on 2015-01-07
9
214 Views
Last Modified: 2015-01-07
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?
0
Comment
Question by:Jay Williams
  • 5
  • 4
9 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40535581
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40535611
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
 

Author Comment

by:Jay Williams
ID: 40535625
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40535635
only to row 1000.., i just placed it there :-)
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Jay Williams
ID: 40535658
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40535668
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
 

Author Comment

by:Jay Williams
ID: 40535736
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40535757
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
 

Author Closing Comment

by:Jay Williams
ID: 40535853
Thanks a lot, Rey. Works like a charm.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
Skype is a P2P (Peer to Peer) instant messaging and VOIP (Voice over IP) service – as well as a whole lot more.
Viewers will learn how to use the Hootsuite Dashboard.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now