Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
222 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40535635
only to row 1000.., i just placed it there :-)
0
 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Healthcare organizations in the United States must adhere to the guidance of both the HIPAA (Health Insurance Portability and Accountability Act) and HITECH (Health Information Technology for Economic and Clinical Health Act) for securing and protec…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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