Replies

I just had a similar problem with my Personal template. I found this tip on the Web and it worked for my problem. try saving the book as an xlsa.

Hope it works for you

TD

Dear Dansparks81

Welcome to the Lounge.

Attached is an Excel File with a Macro that should do what you wanted.

Please note the Macro needs the following or it will not work.

The path name must be in Cell B2
All file names must begin at Cell D6

You will need to add the missing worksheets to the file.

Lastly the Macro will fail if for any reason you add or delete rows or columns in the worksheet containing the filenames

Good luck.

TD

Perhaps the below can link can provide assistanc

http://www.jpsoftwaretech.com/using-excel-vba-to-set-up-task-reminders-in-outlook/
Also from the site
About JP
I’m just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there.

TD

Steve:

Yes you can link. For closed files you will need the full path name. Since you already have the full path names all you need is to use Excel’s HYPERLINK Function.

Example:

Cell A1 has C:documentsPlanets.html
In Cell A2 put the following formula =HYPERLINK(A1)
Copy the formula down as many rows as needed.
Cell A2 will turn blue and be underlined
if you click on the cell it should open the file in it’s associated program.
TD

I think this is what you are looking for.

It creates a table from your data and then uses COUNTIF and SUMIF to get needed information

Was not sure if you wanted number of trades each month or $ amount of profit each month so I put in both.

Hope this helps.

td

Steve:
Welcome to the lounge. The good news is Excel should be able to assit you. If the files remain under say 10,000 Excel has most of the tools you will need. With Excel Open select “Data” to Open the Data Ribbon. There you will find several Groups and Buttons to manlipulate information.

If all the files are in one Folder what I would suggest is to Open Windows Exlpoyer go to the folder and select all the files Ctrl + A. Next while holding down the shift key right click the mouse and on the pop up select “copy as path” The go to Excel and Paste it to a workbseet. This will give you the files names. Next you can use any blank column or columns to expand the identificaiton of a file such as in one column the type of file such as “Auto-Generated”, “Old html”, “New html”, etc. By addiing such items you can “sort” or “Filer” data. You control what ID’s you want for each file.

Attached I have included a sample of what Excel can provide. The Workbook has the Filter Function of Excel Activated.

Lastly, if you should need further assistance or have additional questions just post it here.

Good Luck
TD

What I provided should work.

I ran the macro and got the same results as in your Output 1; Output 2; and Output 3
Attached are MyData01.xlsx [data file one]
MyData02.xlsx [data file two]

Aslo attached are the blank output files which are Output One Output Two and Output Three – to use the macro these files must be opened and their names must be Output One.xlsm; Output Two.xlsx; Output Three.xlsx.

In Output One.xlsm you must entet the full pathname of each file you want opened and copied to the Output Workbooks. [One Two Three]

In the sample I have set up only two workbooks; but you can put as many pathnames as you want, but they all must be in column C and there can be no blank rows between the names.

Once you have put the full filepath of the exact location of the files on your computer in C15 and C16 AND you have open Output One; Output Two and Output Three run the macro.

You should get the desired results in the desired sequence.

Good luck
TD

Without VBA here is a possible solution:
First I think you should focus your attention on Sheet2 since it has only 30 rows.
Make a copy of sheet2 worksheet and give it a name like “original data” as a backup
1 Next, sort sheet2 for Column A (A to Z and smallest to largest)
2 Create a range name for sheet2 $A$1:$A$30 by example “INDEX”
3 Next on sheet1 find an empty column Say Aa1
4 Put the following formula in Aa1
5 =iferror(vlookup(A1,Index,1,False),”No Match”)
6 Copy formula down for each row in sheet1
7 Select all the formulas in Col Aa and select a Data – Filter
Now you can use the filter to create custom reports
Example to show only items with match put a filter such as “No Match” or deselect it from items list
which will hide all rows that have no match in sheet2
After the filter in ON you can select the entire visible range and on the Home Ribbon put a fill color. This will only i i impact the visible items.
You can also “Bold” the visible items if so desired.

To see all information just clear the filter

Lastly when a filter is on if you highlight all the visible data and press F5 – Special – Visible Cells Only
You can then select copy and find a place where you will paste the data and have a static report for your data

Hope this helps.

TD

Below is what I think you are looking for.

Prior to running the macro “MyCopy”

1 You will need to have open 3 Excel files with one worksheet
A – Test of Master.xlsm
B – Test of Master2.xlsx
C – Test of Master3.xlsx

2 The Macro is set to begin in the Workbook “Test of Master.xlsm”
Worksheet 1 Cell “C15”
This cell and all the cells below it must have the full pathname of the Worbooks with Data

3 The fastest way to get this pathnames is to use Windows Explorer and go to the Folder with the files
Select all the files CTRL + A
Hold down “SHIFT” KEY and Right Click mouse.
Select “Copy as path”
Retun to the Workbook “Test of Master.xlxm” go to Sheet1 Cell “C15” and paste the clipboard

4 Put the following Code in the Test of Master.xlsm

Sub MyCopy()
Dim Aa As Integer
Dim Ab As Integer
Dim Fname As String
Dim Fname1 As String
Aa = 1
Ab = 15
Worksheets.Item(1).Activate
Cells(Ab, 3).Select
Do While ActiveCell “”
Workbooks(“Test of Master.xlsm”).Activate
Worksheets.Item(1).Activate
Cells(Ab, 3).Activate
Fname = ActiveCell.Value
Workbooks.Open (Fname)
Fname1 = ActiveWorkbook.Name
Worksheets.Item(1).Copy After:=Workbooks(“Test of Master.xlsm”).Worksheets.Item(Aa)
Workbooks(Fname1).Activate
Worksheets.Item(2).Copy After:=Workbooks(“Test of Master2.xlsx”).Worksheets.Item(Aa)
Workbooks(Fname1).Activate
Worksheets.Item(3).Copy After:=Workbooks(“Test of Master3.xlsx”).Worksheets.Item(Aa)
Workbooks(Fname1).Close SaveChanges:=False
Aa = Aa + 1
Ab = Ab + 1
Workbooks(“Test of Master.xlsm”).Activate
Worksheets.Item(1).Activate
Cells(Ab, 3).Activate
Loop
End Sub

5 Run the Macro

Good Luck TD

Dear 6rtury:

Below is the article from the “askwoody” home page. Have you tried the last suggestion?

In other words, can you remove the update KB3008923?

If not I would suggest you Google “KB 2553154″ and see what others have done. Good luck. TD

A R T I C L E

Botch brigade: KB 2553154, 2726958 clobber Excel ActiveX; KB 3011970 Silverlight and KB 3004394 Root Cert both pulled
Posted on December 11th, 2014 at 07:35 woody No comments
But wait! There’s more!

KB 3008923 crashes IE, KB 3002339 still hanging on install, KB 2986475 still pulled.

Details at InfoWorld Tech Watch.

I just had an independently reported crash, from PB:

I wanted to mention that I work for a small company and starting yesterday everyone in the office started getting IE failure messages “Internet Explorer has stopped working A problem caused the program to stop working correctly……. Close Program” I have found that be removing the new update KB3008923 has resolved the problem on all computers.

Thanks, PB

Summersond:
Can you provide a bit more information. How does the user provide the original data? Is it by an Excel Input Box? is it by entering the information in a specific cell of a Workbook? Once the user enters the information do you always want the same file data.xlsx to open? Do you want the Macro to open temp.txt? or just append it?

Looking at the request, it could be far easier to limit the use of VBA and instead use Excel to get this done. What I mean is if a user opens a workbook and there is a hidden sheet or hidden workbook with the data.xlsx information Excel’s string functions can strip the first three digits the a simple vlookup would return the informaiton in Column B of the open data.xlxs Alternatively, using the “On Open” Event you could have VBA open and make active the “data” workbook (Hidden if desired and with a password to unhide). Now a much simpler macro would transfer the data from excel to the txt file.

I am not saying VBA cannot do everything, to me it would make more sense to use Excel and its built in functions to do as much of the work as possible and leave the VBA to finish the task.

bobski
Not sure what you mean by custom? If the date is entered as text by example 09/26/2014 for September 26, 2014
Use the funciton datevalue() which will turn the text back to a serial number. Then just format that serial number using the date format native to your Excel and you should see the date just the same way as all other cells formated for dates.

If however the custom format is something other than text can you please post a sample of what you receive so a solution can be posted.

TD

LL:

Try using CurrentRegion combined with selecting visible cells only and then paste.
Something like the below

‘Filter Sheets and copy rows to MKT
Worksheets(i).Range(“B7:L7”).AutoFilter Field:=9, Criteria1:=Array( _
“Opt1”, “Opt2”, “=”), Operator:=xlFilterValues
‘Worksheets(i).Range(“B8”).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Seleciton.Copy
Put code here to make active workbook MKT and place where data will be pasted
ActiveSheet.Paste

LastRow = ActiveSheet.Cells(Rows.Count, 10).End(xlUp).Row
For Each targetrow In Worksheets(i).Range(“B8:L” & LastRow).Rows
If targetrow.Hidden = False Then
For J = 2 To 11
.Cells(sourcerow, J – 1) = Cells(targetrow.Row, J)
Next J
sourcerow = sourcerow + 1
End If
Next targetrow

Hope this helps

TD

Dear Yangyang

Welcome to the lounge.

Everyone want to help and I know excel can do this, but the data provided is very difficult to understand. What is “Summarization Record”? These entires appear to cancel out other records. See amounts recorded on 9/15 for minus 249.71 and minus 513.00 and the summarization record on 9/17 of positive 762.71 they total to zero but have no common reference in Col H or any other columns. In other words without a common reference Excel could never match the 3 amounts.

Can better explain what needs to be matched?

Regards,

TomD

Could a Pivot Table give the information you want?

First highlight all data then on Ribbon create a Pivot table. Drag and Drop “Table” as rows and “Fields” as columns. Then place Fields as value
In other words, put fields in twice. Hit finish. Only difference from above is the table will show ” 1 ” rather than ” X ”

Good Luck