98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Extract data from saved file
Extract data from saved file
Resolved · Low Priority · Version 2013
Fri 20 Oct 2017, 17:30Delegate Nasir said...
Nasir has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Extract data from saved file
Hello everyone,
Not an expert in VBA coding but have created few basic macros with loop function. Just not sure how to do below in my coding. I hope you know the coding I can use.
1. how to extract data from a saved file located in a shared drive e.g. C:\Users\Nasir\Documents\freelance\sales. only extract data from file name ENDS with "Detail" and saved TODAY. data range to copy A:AH1 columns and paste value in active worksheet in say tab named "Raw Data"
2. save worksheet to a shared drive e.g.:\Users\Nasir\Documents\freelance\sales, file name start with "DMO Turnover" and ends with todays date in format say "2017OCT15"
..and lastly
3. look for column with name begins with e.g. "trades late by 2" and copy the data underneath that including the column name and past value in another location in same worksheet.
if it is easy you can call me on 07710056521 or email nasira254@gmail.com. any help will be greatly appreciated.
many thanks
Nasir
Mon 23 Oct 2017, 11:50Trainer Jens said...
RE: Extract data from saved file
Hi Nasir,
Thank you for the forum question.
I am sorry but it is outside the scope of this forum to develop tools for our clients. I can give you some hints, but it is complicated VBA code you need to be able to do what you want.
You will need knowledge of using the Dir function to reference the source workbook. You will have to use a variable to store the date (you can use the Date function to get the current date).
The FileDateTime function returns the date value of the source file.
You want to copy and paste a huge range into the destination sheet. This will give you a hard time. I will use an Array to do this.
You will need the Format function to save the destination workbook with the date format you want.
You will need the Find function to find "trades late by 2" and an Array and the CurrentRegion object to move the data to the destination sheet.
You will need a number of variables.
I hope this can guide you in the right direction.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Highlighting only Text cellsTo select onlt text value cells in a spreadsheet, click on Edit-Go to(F5) |
