Free Microsoft Excel 2013 Quick Reference

Filecopy Tool

This one's a bit less sophisticated than my 'Additional Utilities', but it still may be useful to some of you (plus I can fit it on the site, so there's no need to email for it).

The FileCopy Tool is an Excel-based application that allows the copying of files to be automated. It can be used to copy individual files or entire folders, and can also be configured to search folders for items to copy using standard wildcard characters. It can also rename the files / folders when they are copied across, and create folders to contain the copies if the required folders don't already exist.


Post your answer or comment

comments powered by Disqus
I am trying to make a macro that copies a file per row to a folder specified by a cell in the row (and then calls a command line tool on it). I am using FileCopy to copy the file, but it is extremely slow even for only 6 rows (I need it to work with hundreds).

I think the problem is that the folders are network folders. Is there maybe another copy file function in VBA that does not block while the operation completes? I could go through all my rows and just copy the files, then go through again and run the command line tool. By the time it reaches the end of the sheet, the first file would be done copying.

Thanks for the reply.

Here is an example

Hi

I am an HR manager trying to find a tool or learn how to create the tool that will allow me to compare where people salaries are in relation to their range. Example. If someone is at $10.00 per hour and the range is a 5 band range I want to be able to compare where they are in relation top each band as a percentage. I can do this by copying and pasting, but I am looking for something that would allow me to conditionally format the spreadsheet and auto fill the cells and then highlight anything above 100%. Thoughts?

I can post what I have done so far if that would help...

Thanks...

I am relatively new to using Excel VBA. I am trying to write a tool for use unpivoting data to put it in database form. My goal is to have the program to be able to do data of varying dimensions. For example, I would like people to be able to copy and paste in data with different numbers of rows and columns and have it still work. I can't figure out if the best way to do this is to have the program prompt the user for the number of rows and columns in the spreadsheet, or if there is someway for it to automatically do this. Once again I'm pretty new so go easy on me! Thanks for any help you can offer.

I'm using Office 2007 under Windows 7. I have a button on an Excel worksheet which shells to the Snipping Tool to allow the user to capture part of the screen:-

	VB:
	
Shell "c:windowssystem32snippingtool.exe", vbNormalFocus 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This returns a Run-time error 53 File not found. The same happens if I run that command from the Immediate window (or in fact from MS Access). However if I put Notepad.exe in the Shell command, Notepad starts without a hitch.

SnippingTool.exe is definitely in C:WindowsSystem32 and it runs okay from the Start Run... button. It also works okay under Vista.

Why should Notepad run but not the Snipping Tool? Has anyone else come across this? Can anyone with Windows 7 confirm their system exhibits the same behaviour?

TIA,
R.

Hello,

Here I provided an example of my workbook. drawtime.xlsm
I use the scribble tool to draw on my workbook for specific shapes. The limiting factor is I only use 2-4 colors when drawing. I'd like a way to control the color property of the auto shape that is drawn by way of choosing the color that's wanted to draw in. I can't seem to get the find what I'm looking for. The purpose of this is to allow freedom of choosing the color prior then going through the excel system that is already set up.

Thanks

Hi every1,

I have been asked to write a short report comparing the use of the macros and tool box features (combo box, list box, spinner) in automating some processes in spreadsheets.

What is the main difference between them to compare?

Thanks in advance,
Jacob

Hello Again,

Iam need to prepare an template for medical underwriting pricing tool.

When we plugin our data in sheet plug-in data then the following should be done.

- Check the class first
- Then check the age
- Then check relation
- The check gender
- Then check nationality (Loading & Discount)

Based on these following above first 4 fields it should calculate NRP then based on nationality the loading or discount will be appear in columns on sheet Template.

Please can anyone help me to make this file work as per requirement.

Thanks in advance for your help.

Hi All,

I have a list that has been created by using the validation tool (Data>Validation)

Is it possible that when someone wants to choose something from the list they could say type the first few words and it would automatically show.

I.e you type "holi" or atleast part of the word and the cell shows the whole word.

Thanks steve

Hello once more. A quick one but has me confused.


	VB:
	
 
FileCopy "w2k8file-cogeneralForward Book SystemForwardBookMid.csv", "w2k8file-cogeneralForward Book SystemForwardBookMid.txt"


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

works from one workbook but not when used in another. Can;t see why but get the error message in the thread title.

Any ideas?

Hi All,

I have a print preview macro that works fine other than in Excel 2007 where when the code is run the Print Preview toll bar is unavailable, the only way to close the window is using the X in the top right hand corner.

Can any one advise code changes necessary to allow users to view the print preview tool bar in excel 2007.

Thank you for any help, my code is below.


	VB:
	
 PrintPreviewBandW() 
     
    Msg = "Are you sure you want to preview selected sheet(s) ?" 
    Style = vbYesNo + vbExclamation + vbDefaultButton1 
    Response = vbNo 
    Response = MsgBox(Msg, Style, Title) 
    If Response = vbYes Then 
         
        On Error Resume Next 
        Application.ScreenUpdating = Hide 
        FormPrintOptions.Hide 
         
        If FormPrintOptions.CheckBoxU1.Value = True Then 
            Call HideB_Rows 
             
            With Sheet1.PageSetup 
                .BlackAndWhite = True 
                .PrintArea = "Area" 
                .LeftHeader = "&12Scenario: " & [file_name].Value 
                .RightHeader = "&12Property: " & [i_property_name].Value 
                .LeftFooter = "&12Plan to Profit: Version " & [Version_software].Value 
                .CenterFooter = "&12Consultant: " & [i_consultant_name].Value 
            End With 
            Sheet1.PrintPreview 
            FormPrintOptions.CheckBoxU1.Value = False 
        End If 
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Groove 2007/Windows 7: Files Tool not downloading in Standard Workspace

I just purchased a new laptop with Windows 7 Home Premium. Using a fresh install of Groove 2007, I downloaded workspaces from my colleague. The workspaces download (kind of) and appear in the Lauchbar. However, when I open the the Files tools are not there. In one workspace, I also have a calender, which does appear and work fine, it is just the file tool that does not. I checked the size of the workspaces after downloading and they are significantly smaller than the workspaces should be (only a few MB out of several hundred). Also, looking at the Communications Manager, next to a couple of the workspaces it says "0 bytes left to transmit, 104bytes left to receive" (30bytes left to receive on the other one). The other 2 are 'idle'. These remain after downloading several days ago.  I have tried deleting the workspaces, restarting windows and groove and redownloading the workspaces (as suggested on the groove discussion board), but this did nothing.  Is this possibly an issue with 64bit OS? I am considering upgrading windows to get the virtual xp since I am at a loss at this point, but don't want to if its not needed. PLus it was my understanding that only file sharing work spaces are effected by 64 bit.  Any help is appreciated! Thanks!

Lookup List Function -- Entisoft Tools 2.0 Object Library

Previous || Up || Next --> Lookup Function List Class Public Function Lookup( _ ByVal vValue As Variant _ , Optional ByVal vCompare As Variant _ ) As Boolean

Lookup SymbolTable Function -- Entisoft Tools 2.0 Object Library

Previous || Up || Next --> Lookup Function Symbol Table Class Public Function Lookup( _ ByRef rValue As Variant _ , ByVal vName As Variant _ , Optional ByVal vCompare As ...

I placed an add-in (.xla file) with a "function" and a "sub" into C:Microsoft Office XPOffice10Library. When I go to Tools-->Add-Ins the Add-In doesn't show up. When I browse the file it says it already exists and still doesn't show up. The strange thing is that the "function" does work, but the sub (which is a sub worksheet_change) doesn't run.

I also tried doing the steps as they are laid out in http://www.ozgrid.com/VBA/excel-add-in-create.htm and ended up with the same problem.

The function works when I open up a brand new Excel application and use Book1, so I know the function isn't embedded in the sheet.

Thanks.

how can I change via VBA the chart options in tools--> options-->charts

I need helping putting together a tool that will help me to report out gaps between demand versus supply of human resources. Here are the details of the input data:

There are 8 Functional Managers
For each Functional Manager there are a list of Projects (# varies)
For each Project there are 8 Resource Skills tracked
For each Resource Skill the Assigned Resources (listed by name) + the TBD Resources are listed (Assigned Resources + TBD Resources = Demand for Resource)

What I need to calculate is:

For each skill (8) a chart of the Demand versus Allocated Resource

The reason why I'm having a hard time doing this is b/c the for each skill the number of Assigned Resources varies so I don't know who far to go down in the table to count the number assigned resources for a particular skill.

Heres's an example

PROJECT 1
MARKETING (SKILL)
Sarah Jones 1.0
Bill Smith 0.5
Carrie Parks 0.25
TBD 2.5
ENGINEERING (SKILL)
John Doe
Nick Foley
TBD

Hi all,

I have two extremely large files. They contain lists of company names from two systems that are independently maintained hence there is no uniform maintenance between the systems.

I need to find matches for the data in the one system from the data in the other system. I was hoping to put together a "mapping tool" of some sort that would suggest to me some options that I could choose from.

Eg in the one system I have "Standard bank" and in the other system I have STANDARD BANK STOCKBROKERS (CI) LTD ; STANDARD BANK STOCKBROKERS (CI) LTD XXXXXXPARENTXXXXXX; STANDARD BANK PLC.
I was hoping that I could use VBA to create a script that would run through each row of the data and create an output that for each record produced a list of the closest matches from the other systems data that somebody could then manually go through and select the best match.

Has anybody done something like this in the past. Is it infact possible?

Thanks,

Marcus.

Hi Guys,

I need to create a tool bar with some features that I don't know how to implement.

I got the code for the tool bar that would 'travel' with my workbook BUT I want to know how can I do the following:

1. Have a button on the bar that can't be pressed;
2. Have two dropdown lists (like the for the font size) with names of months;
3. Have a default value on the dropdown list;
4. Assign a tooltip to a button;

Thanks in advance for you replies

Hello,

Could you please suggest me the VBA coding to link excel data to IE-9 tools? Actually I take data from excel sheet and upload to one tool called "momo" which is a web based tool. Everyday I perform this for multiple sheets. So is there any coding wherein we can link excel to IE tools. Please suggest.

Hi, I have a pivot chart which has a filter controlled with a combo box (not sure this matters) On this same Dashboard, I have used the camera tool to take a picture of of the range of cells encompassed by the chart. When I change the options on my combo box, the Pivotchart changes, but my camera's snapshot shows only the blank cells under the chart. Oddly if I select the chart, then select the picture and move it, it seems to refresh. I have coded a macro to do just that...but it does not work unless I manually do it. Have I angered some ghost of excel? I really can't figure this out! Thank you, Tim

I've downloaded Reconciliation Tool from CFABS for eliminating duplications in different mailing lists. Now that I see them in the window, I want to save the results to a different worksheet. When I click "save" it saves the entire sheet again to the same location. Any way to "save as..."?

I would like to know what is the formula used under Conditional Formatting tool in order to do the following... please see attachment... CondiFormating_Help.xlsx.

I would like to be able to insert which ever value in a cell in order to affect the other cells format.

Thanks,

xluser26

HI

I am having difficulty trying to define dynamic ranges in the Histogram tool through VBA.
Specifically, say
I have N columns of data on Sheet 1 and I want to plot the histogram for each column.
I have defined the corresponding N bin ranges on Sheet 2

What I want to do is to write a VBA subroutine which will calculate the N histograms sequentially.

I'm using the floowing code:

Sub hist()
Dim i As Integer
For i = 1 To 2
Application.Run "ATPVBAEN.XLAM!Histogram", _
Range(Range("C7").Offset(0, 0), Range("C7").Offset(200, 0)), _
Range(Range("C7").Offset(0, 10)), _
Range(Range("I7").Offset(0, 0), Range("I7").Offset(21, 0)), _
False, False, False, False
Next i
End Sub

Can anyone see what I adoing wrong with this?

In one of our department PC Auditing Tool bar Precendent is not working other options of Audting Tools are woking Like comment etc.

If we use comment first .. it wil show us arrow pointing to other sheet but... if we click on precedent tool to see arrow it is not showing anything

I have remove the office and installed everything again nut it still not working.

Please help

Regards
Sangeeta


No luck finding an answer? You could always try Google.