Solved

Extract Data and filename Macro

Posted on 2016-07-28
20
98 Views
Last Modified: 2016-08-04
In a folder I have multiple files all with the same format

In each file i have a tab called "statement"

data have to be pulled from B,C,D,E,F and G columns

Steps:
once file directory is selected
from all the files of statement tab
Copy Rows of B,C,D,E,F and G rows which is not 0.00 or copy all with values and 0.00 can be deleted in output file
In an output file copy file name and paste as values what has been copied in the above step

please let me know if I am not clear.
attached input file and sample output in the output tab

I Could see some help in below sites but do not know how put across

stackoverflow.com/questions/29609134/save-excel-worksheet-to-specific-folder-and-filename-based-on-same-cell

ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/

superuser.com/questions/441446/automatically-reading-data-from-separate-excel-files
Statment.xlsx
0
Comment
Question by:Nirvana
  • 9
  • 8
  • 3
20 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 41733129
in your sample workbook, there isn't much difference in the B:G cell data, relative to zero values.  What am I missing?
0
 

Author Comment

by:Nirvana
ID: 41733192
I get this file every 15 days and the values in currency and combination changes for example i might receive values in AUD_SEK; GBP_SEK; HKD_SEK; NOK_SEK;  and SGD_SEK; NOK_SEK; PLN_SEK in other fortnight

while cell references are remain standard/same the values will be in different cells

and what i have provided here is the sample data. hope i am clear and if you are referring to the output file and file two sorry its my mistake the amount and currency might have changed
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41733235
I'm a pretty visual person.  Please post a workbook or two that shows me the expected input and expected output.
0
 

Author Comment

by:Nirvana
ID: 41733310
Sure. in the attached file i have two tabs as input (2) and Input (3) where i have some number in income, cost, receivables and liabilities. what i would need is where ever the numbers are there for each of those categories (income, cost etc.,) have to be pulled into a sheet. please not input files are different workbooks in a directory

please do let me know if i am not clear still
Statment.xlsx
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41733424
Will these input files have exclusive categorical non-zero values?

If you have posted representative data, then I can expect that no two (or more) workbooks will ever have a non-zero value in any given category, such as I see in cell D12.
0
 

Author Comment

by:Nirvana
ID: 41734577
If I understand correctly, it can be any combination of data. i will have more than 10 workbooks and some might have value in one category and might be zero in others

The only thing that i am looking is

File name:
Category (example income/revenue etc.,)
Currency (example EUR/SEK)
local and other currency
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41734595
1. Do these values need to be summed?
2. Are all workbooks in the same folder?
0
 

Author Comment

by:Nirvana
ID: 41734786
no these values need not be summed and yes all files in the same folder
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41734878
these values need not be summed
Then how do we treat the non-zero data in light of
any combination of data
If not summed, then options are
* first value wins
* last value wins
* random value wins (three or more scenario)
* error message
0
 

Author Comment

by:Nirvana
ID: 41735121
I need the data to be copy pasted in each row this a a consolidation report

here is how it works

i have folder named "Analysis"

Analysis
       |->File 1
       |->File 2
       |->File 3

In File 1  sheet "statement" tab I have Income ,Costs, Receivables, Liabilities in col B, Currency combination in Col C (example) AUD_SEK and amount in column D:G

output will Category from Col B, Currency from Col C, and amounts from Col D:G

if it takes log time to pick only non zeros even if it can consolidate all files and only tab statmen to one sheet that also will be fine

"
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 45

Expert Comment

by:aikimark
ID: 41735353
Picking the non-zero values isn't the issue.  The issue is what to do if a cell has non-zero value in multiple workbooks.

For example, Workbook: File 1.xlsx, worksheet: Input, Cell D12 = 1234.56
Workbook: File 2.xlsx, worksheet: Input, Cell D12 = 789.00

What should the D12 cell value be in the output?
0
 

Author Comment

by:Nirvana
ID: 41735357
those will come in different columns of the out put file

say Workbook: File 1.xlsx, worksheet: Input, Cell D12 = 1234.56 will come in Column D
and Workbook: File 2.xlsx, worksheet: Input, Cell D12 = 789.00 will come in Column E
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41735365
You've completely lost me.  I haven't a clue what you are trying to do.
0
 

Author Comment

by:Nirvana
ID: 41735430
its a simple thing dont know whats so complicated

get a data from different files into a single file by copy pasting values
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41735437
At this point, I recommend you click the request attention link and ask for more expert involvement.  I won't get to visit this question until after the weekend.
0
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41735457
Hi Uday,

You may try something like this.
In the attached, click the button called Extract Data to extract all the relevant data from Statement Sheets from all the files from a selected folder.
The code will prompt you to select a folder where files are saved.
Sub ExtractDataFromStatements()
Dim wb As Workbook, swb As Workbook
Dim ws As Worksheet, sws As Worksheet
Dim slr As Long, lr As Long, lc As Long, i As Long
Dim sRng As Range, sCell As Range
Dim fso As Object
Dim folder As Object
Dim file As Object
Dim SelectedFolder As String

Application.ScreenUpdating = False
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")

Set fso = CreateObject("Scripting.FileSystemObject")

With Application.FileDialog(msoFileDialogFolderPicker)
   .Title = "Select A Folder!"
   .ButtonName = "Confirm"
   If .Show = -1 Then
      SelectedFolder = .SelectedItems(1)
      Set folder = fso.GetFolder(SelectedFolder)
   Else
      MsgBox "You didn't select a folder.", vbExclamation, "Folder Not Selected!"
      Exit Sub
   End If
End With
For Each file In folder.Files
   If InStr(file.Name, wb.Name) = 0 And Left(fso.GetExtensionName(file), 2) = "xl" Then
      Workbooks.Open file
      Set swb = ActiveWorkbook
      Set sws = swb.Sheets("Statement")
      slr = sws.Cells(Rows.Count, 2).End(xlUp).Row
      For i = slr To 10 Step -1
         If Application.Sum(sws.Range("D" & i & ":G" & i)) = 0 Then
            sws.Rows(i).Delete
         ElseIf sws.Range("D" & i).Value = 0 Then
            sws.Range("D" & i & ":E" & i).Delete shift:=xlToLeft
         ElseIf sws.Range("F" & i).Value = 0 Then
            sws.Range("F" & i & ":G" & i).Delete shift:=xlToLeft
         End If
      Next i
      slr = sws.Cells(Rows.Count, 2).End(xlUp).Row
      Set sRng = sws.Range("B9:E" & slr)
      
      lc = ws.Cells(4, Columns.Count).End(xlToLeft).Column
      
      If lc = 1 Then
         sRng.Copy ws.Cells(4, lc)
         ws.Cells(2, lc).Value = Left(file.Name, InStr(file.Name, ".") - 1)
      Else
         sRng.Copy ws.Cells(4, lc + 2)
         ws.Cells(2, lc + 2).Value = Left(file.Name, InStr(file.Name, ".") - 1)
      End If
      swb.Close False
   End If
   Set swb = Nothing
Next file
ws.Cells.WrapText = False
ws.Columns.AutoFit
Application.ScreenUpdating = True
MsgBox "Task Completed.", vbInformation, "Done!"
End Sub

Open in new window

ExtractData.xlsm
1
 

Author Comment

by:Nirvana
ID: 41735483
It worked brilliant. my files have some merged cells and formulas how can unmerge all files and paste specials as values in the result file.

You are a genius.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41735484
Thanks for the feedback.
Can you upload one such sample file?
1
 

Author Closing Comment

by:Nirvana
ID: 41735753
Simply Genius.Thank you so much
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41735754
You're welcome Uday! Glad I could help. :)
Thanks for the feedback.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

708 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

18 Experts available now in Live!

Get 1:1 Help Now