Free Microsoft Excel 2013 Quick Reference

zip workbook file by using "Send to compressed zipped folder"

Is it possible to save/send a workbook to compressed zipped folder by vba.
Using standard windows zip method (refer right click file option).
As all users will have this feature, as i dont want to use a thrid party zip app.

So i and other users can automate zip of workbook into an email via macro.


Post your answer or comment

comments powered by Disqus
Is it possible to Compress a Folder/File or create a Zip File Dynamically?

I have realized that VBA has a great potential and I have managed to create Files and Folders Dynamically using VBA coding..ofcourse with a lot of great help from the forum..

Going forward in saving time is it possible to Compress the Created Folders or any any Folders in a Given Path..

Like a WinZip file which can be an email attachment or on Pen-Drive..

Warm Regards
e4excel

Anyone know of a way to create a linked workbook file name using cell
variables.

I have a workbook that will pull in data from a .cvs file generated daily.
The date (yyyymmdd) included in the file name changes each day. Was looking
for a way to grab the date from the master spreadsheet header and build the
..csv file name. Was hoping to not have to manually create a new file name
for each day of the month. Something along the lines "daily_data_(cell
R1).cvs" == daily_data_20050101.cvs

Thanks

RichT

Anyone know of a way to create a linked workbook file name using cell
variables.

I have a workbook that will pull in data from a .cvs file generated daily.
The date (yyyymmdd) included in the file name changes each day. Was looking
for a way to grab the date from the master spreadsheet header and build the
..csv file name. Was hoping to not have to manually create a new file name
for each day of the month. Something along the lines "daily_data_(cell
R1).cvs" == daily_data_20050101.cvs

Thanks

RichT

In my code below I have this line of code
Case "TD"
strfilename = "TomsblackibmTomsProposals" & strfilename I would like to send this file by email if possible instead of to his computer. The user recently went from a desktop "in the office" to a laptop "all over the place".

Here is my code
Code:
Sub Save_and_SaveSalesman()


Dim strPath As String, strPath2 As String, CurrPath As String
    
    Dim WB1 As Workbook
    Dim WB2 As Workbook
        
    Set WB1 = ActiveWorkbook
    
    'First thing, save my work
    WB1.Save
    
    CurrPath = WB1.Path
    
    'ASSUMING THAT C6 and O3 are BOTH in WB1
    'move this line HERE: only do this once, and concatenate in the Select..Case later
    'doing thsi inside the Select..Case pulls values from WB2, which might cause errors...
    strfilename = Range("C6").Value & Range("O3").Value & ".xls"
    
    strPath = "C:Documents and SettingsOwnerMy DocumentsCompleted Proposals"
    
    strPath2 = "C:Documents and SettingsOwnerMy DocumentsSurface Systems"
    
    On Error Resume Next
    
    'I then want to save my file as "Proposal" and the number in Cell O3
    WB1.SaveAs Filename:=strPath & strfilename
    
    On Error GoTo 0
    
    'I call this workbook "new_file"
    'as long as you use the WB1 object, you should not need to store the name... - PES
'    new_file = wb1.Name
    
    'you should never need to select anything... - PES
'    Range("F2").Select
    
    'I want to open "Proposal for XL" so I can make a 2nd copy to the salesmans computer
    Set WB2 = Workbooks.Open(Filename:=strPath2 & "Proposal for XL.xls")
    
    'I have to save the "new_file"
    'WHY??? you did this above - PES
'    Workbooks(new_file).Save
    
    'Workbooks(new_file).Close
    'Here is where i need to choose the computer for it to go to. As well as give the file a name that the salesman
recognizes. C6 is customer name and O3 is the proposal number
    'Select Case WB2.Sheets("FRONT").Range("C2").Value
    Select Case WB1.Sheets("FRONT").Range("C2").Value
        Case "MD"
            strfilename = "MIKESRGATEWAYMikesProposals" & strfilename
            
        Case "TD"
            strfilename = "TomsblackibmTomsProposals" & strfilename
            
        Case "DJ"
            strfilename = "DAVEJONESDavesProposals" & strfilename
            
        Case "CP"
            strfilename = "Chuckscomputerdaily" & strfilename
            
    End Select
    
    WB1.SaveCopyAs Filename:=strfilename
    
    WB1.ActiveSheet.Shapes("Button 53").Visible = False
    
    ChDir CurrPath
    
    Application.ScreenUpdating = True
    
    WB1.Close
End Sub


Thank You,
Michael

I have a workbook that has 100 sheets. I want to save each sheet as a
separate xls file. Even though I could save each by copying into new
worksheet and save manually, I am looking for a way to automate it by using
script. Can anyone help?

I recently posted that I am having a problem with a custom toolbar that I have attached to my workbook; the problem being that when the macros are disabled I cannot delete this toolbar after the workbook is closed, thus still being available to the user in a non-functional form, which I don't want. The code uses the BeforeClose event to delete the toolbar to accomplish this, but obviously not when the user disables the Macros. Is there a way to use VBA to create the toolbar in the first place? I was discourage because the help file indicated:

"All host applications have an extensive interface for adding and designing custom toolbars (adding built-in buttons, adding macros as buttons, even adding pop-up controls to toolbars). The design-time changes you'll usually make from Visual Basic code are ones that add or modify combo box controls. Otherwise, working with toolbars in code is almost completely limited to making run-time changes (changing the button state, changing the button appearance, changing the button action, and so on)."

Note: I am not looking for a way to bypass the security feature, just to make the toolbar unavailable when macros are disabled by using code to create the toolbar, or by some other, creative, means. If anyone has any ideas for me it would be greatly appreciated.

Thanks,

Hi,

I have office XP on a Windows xp pc. However, I have outlook 2003. I want
to be able to send the body of an excel file through e-mail by selecting File
=> Send To => Mail Recipient. However, I am getting an error. It says
"Excel could not start your e-mail program. Check your network connections.
Make sure Office is installed correctly, your e-mail program is set up
correctlly, and you can connect to your e-mail server." Outlook is working
fine as is the connection to our ISP. I think this may be happening because
outlook and excel are different versions. I don't want to downgrade the
outlook or upgrade the excel. Can anyone help?

Thanks,
--
Chuck W

Hi There-

I have about 100 Excel (2007) workbook files, and I want to copy only one worksheet (named "Analysis Format") from all of them in to a single Master Workbook.

I would like this Master Workbook to contain one Worksheet tab for each Workbook I am extracting from. Each tab I would like to be named as a number , i.e. 1 through 100. (In other words, in the Master Workbook I want the data from each Workbook on a seperate Worksheet).

I want to copy just the values, not formulas.

I have all the Workbooks saved in the same folder. It would be easiest if I didn't have to have them all open already.

I have searched through the FAQ section and can't find quite what I'm looking for. If you have a link to somewhere this has already been answered, much appreciated.

I will personally bake and send you a box of cookies if you can solve this issue for me- no joke! I have been trying to find a solution for quite some time...

Thanks very much.

Send to (as an attachment) in Office 2007 applications does not work when Outlook 2007 is open

I am having an issue using the Send To email as an attachment in Word, Excel, and Project when Outlook is open. I am currently running the Office 2007 SP2 product suite. The message is "General Mail Failure. Quit Microsoft Office Excel and restart mail system."
The Send To function works as expected if Outlook is closed. The document will appear in an email as an attachment. I can also right click on a file and select send to if Outlook is closed. When Outlook is open the right click on file and send does not open an email with the attachment...it doesn't do anything.
I have seen alot of people having the generic email error and their issue is resolved by checking the default programs and settings. In my case the settings are correct and its only an issue if Outlook is already opened.
Anyone else have this issue?

When I use Windows 2003 Terminal Server and Exchange 2003 with Office 2003
and attempt to send a .xls document from Excel by choosing File, Send To,
Mail Recipient (as Attachment), the email gets generated, however, it sits
in the Outlook 2003 outbox until I manually click on Send /Receive. I am
connected directly to an Exchange 2003 server and I don't experience this
problem with other types of files using Send to. Help.

John Goins

Hi,

I have office XP on a Windows xp pc. However, I have outlook 2003. I want
to be able to send the body of an excel file through e-mail by selecting File
=> Send To => Mail Recipient. However, I am getting an error. It says
"Excel could not start your e-mail program. Check your network connections.
Make sure Office is installed correctly, your e-mail program is set up
correctlly, and you can connect to your e-mail server." Outlook is working
fine as is the connection to our ISP. I think this may be happening because
outlook and excel are different versions. I don't want to downgrade the
outlook or upgrade the excel. Can anyone help?

Thanks,
--
Chuck W

Note: I posted the PowerPoint version rather than the Excel version of my macro. My bad for posting here!

I created this macro based on a similar Word VBA macro from Graham Mayor that does this for MS Word documents. This macro takes a file like "file.pptx" and saves it as "file-2011-08-29-v001.pptm". Running the macro again increments the file name to "file-2011-08-29-v002.pptm" and saves it.

Save this code in a VBA module and attach it to a Quick Access Toolbar icon of your choice. It will only work within .pptm files.

I use this to keep track of versions of presentations when I make changes or when I send to others to make changes.

Graham Mayor's original MS Word VBA code to so this is at
http://www.gmayor.com/save_numbered_versions.htm

Hope it is useful to someone.
Kurt

Hello

I have a workbook where I want to hide one worksheet but then make it visible if you enter the password. I use a Private Sub Workbook_BeforeSave to hide this worksheet when the file is saved to make sure on opening it is not visible and everything is working great.

Unfortunatly there seems to be a way that will keep the worksheet visible. If I have the worksheet visible and then use send to mail recipient as attachment, the file will be sent and once received, can be opened with the worksheet visible.

Is there a way to detect if the 'send to' option is chosen so that I can then use some sort of workbook function beforeSendTo to hide this worksheet?

Any tips really appreciated thanks.

Hi everybody.

My problem relates to this post
http://www.ozgrid.com/forum/showthread.php?t=63160
I have managed to write simple but effective code that allows only one password to be enetered to open a SS & offers 3 levels of protection, limited permission to enter data, read-only & data completely hidden.

This is excellent for my needs however I want the user who has limited rw to be able to insert a pic file which is their signature but because the sheet has protected cells it won't allow user to insert picture jpg file.

Is there a way to do this?

I have just thought of a creative solution tho. What if the picture file is already saved in the workbook and by using code to ask to insert pic file it copies from one location in SS to the appropriate cell in the worksheets?

The scenario is that I have fortnightly timesheets in 12 calendar months in 12 separate sheets. When staff member fills in timesheet I want them to insert signature for that f/night indicating thatthey have filled it in & it is correct but because of protection it won't allow me to.

BTW I can post code/file if required

Hello All,

I tried to write some VBA code to loop through all the files in a folder and return the name of the file. (In my current example, all the files are excel workbooks)

Here is the code I have used:


	VB:
	
 Load_List() 
     
     
     
     
    Dim lCount As Long 
    Dim wbResults As Workbook 
    Dim wbCodeBook As Workbook 
     
     
    Set wbCodeBook = ThisWorkbook 
     
    With Application.FileSearch 
        .NewSearch 
         'Change path to suit
        .LookIn = "G:CFOMiddle OfficeDannyFine Tunning for JP" 
        .FileType = msoFileTypeExcelWorkbooks 
         
         
        If .Execute > 0 Then 'Workbooks in folder
            For lCount = 1 To .FoundFiles.Count 
                 
                Set wbResults = Workbooks.Open(.FoundFiles(i)) 
                 
                MsgBox wbResults.Name 
                 
                 
            Next lCount 
             
        End If 
         
    End With 
     
     
     
End Sub 

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

The problem I had with this code is, it needs to open the workbook for every file, which make the code slow.

Is there a way that I can return the name of the file without openning the workbook?

I have googled for quite a while and got the following code:


	VB:
	
 Get_Names() 
     
     
     
    Dim FSO As FileSystemObject 
    Dim FilesDir As Folder 
    Dim CurFile As File 
     
     'Create a variable to store the folder holding the files
    Set FSO = New FileSystemObject 
     
     'Change the string here to look in a different folder (highly recommended :P)
    Set FilesDir = FSO.GetFolder("G:CFOMiddle OfficeDannyFine Tunning for JP") 
     
     
     
     
     'Loop through all of the files in the folder
     
    For Each CurFile In FilesDir.files 
         
         'If the file begins with the word "budget" then rename it
        If CurFile.Name  "Financial Analysis Master File.xls" Then 
             
            Cty_list.AddItem CurFile.Name 
             
        Else 
             
             
        End If 
         
    Next 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, when I tried to run the code, it give me the error message saying: the defined type of the variable are not been defined. Seems to me, that VBA dont have the variable type as Folder, or File.

How could I do this??

Any suggestions will be really appreciated.

Thanks

All,

I am trying to optimize a cooling plant with all loads and efficiencies built into a spreadsheet. I am trying to use Solver to maximize efficiency values by finding the optimum loading of equipment. Basically, I need solver to find the optimum combination of loads on equipment so that the combination of all efficiencies at the iterated load values will result in a optimal plant efficiency.

As before, it seems as if Solver is finding a local maximum, and not the glabal maximum. I know this because I can vary the values in the cells before running solver, and different "solutions" will be produced by Solver.
How do I get it to run through all possible iterations and then compare them to find the one that reveals the maximum efficiency?

Thanks so much for any help!

This may be a simple problem but I haven't found a solution in Excel help. When I use file-open to access a workbook file it takes me to the middle file in the list of files in My Documents. It used to take me to the first file but not now. How do I get it so it won't toss me into the middle?

Hello smart people. I am working with an excel worksheet that I have made into a Template. When users access the template, they fill in data (open now as an .xls file) and then email it to a certain email address.

I would like the template to allow "send to as an attachment" to only a set email address and not allow others. Can this somehow be automated?
Can this be done in an Excel Template?

Thanks for time and consideration!

dreammy

Excel 2002 and Windows XP

For brevity, let's say my D:work folder is marked as compressed. When
I save new .XLS files in it, they're compressed. When I use my text
editor or Notepad to save new text files in it, they're also
compressed. However, if I use the VBE to extract code modules into it,
they're stored UNCOMPRESSED.

This seems to be a nasty dependency between the VBE and Windows, at
least for the specific versions I'm using. I don't want the VBE second
guessing my OS file system settings, and this doesn't fill me with
confidence that there aren't other hidden dependencies between the VBE
and Windows.

The paranoid cynic in me is tempted to believe that this was designed
into the VBE to make using Excel 2002 more difficult using Windows
emulators under other OSs. The jaded cynic in me believes that the VBE
uses overly low level file system access to save .BAS files, but not
necessarily with the intention of screwing up emulators, more likely
due to laziness and bad systems programming.

Hello I posted about this before, but unfortuantely never got the matter figured out.

I have an edited and cleaned up .csv spreadsheet which I use to upload product details to my website. I can also download .csv spreadsheets containing product details from our server here at work.

I need to be able to update the product stock figures on my website file by using the stock figures from my server file.

I'm not and experienced excel user so please forgive my ignorance, but the most feasible way of doing this would seem to be by referencing a product code on sheet 1 and using it to find the same product code on sheet 2. Then find the stock value which corresponds to the product code that has been searched for on sheet 2 and return the value to the desired location on sheet 1.

This seems like one of the most obvious processes that anyone who deals with products on spreadsheets would use and yet I have not managed to turn up any results even after many hours of searching.

If anyone can help me with this, it would be extremely appreciated.

4idy

Hi,

is there any way to extract the text from one cell and distribute its words into other cells by using any formula. not by using text to column option.

example is attached

I am wanting to compile a bunch of data from other workbooks that are all in similar format.

I want to setup a single cell that tells all the other cells which file to reference for each group of analysis I have.

I know how to link specific cells to other workbook files. Although in order to make this run smoother and a lot less manual entering I want to have the ability to have a cell that tells where the file is that the rest of the cells reference.

So if A1 is the cell that references the workbook file that is not part of the current workbook then cells B1, C1, etc will know that it needs ='[Filename]Sheet Name' and the designated cell to pull from the other workbook.

So if B1 is supposed to display cell H113 from the other workbook it should reference A1 and insert ='[Filename]Sheet Name' and then attach H113 to the ='[Filename]Sheet Name'H113

Here is an example with filenames and sheet names included

A1: 'SERVERProperty[ABC Inc - 123 Main St - Property Analysis.xlsx]2012 Season' - This is where the file is located.

B1: =IF(A1<>"",A1 with H113 cell attached to end of string, "")
C1: =IF(A1<>"",A1 with H114 cell attached to end of string, "")

Note: A1 would be entered manually and if possible have the rest of the sheet fill itself out.

Is this possible to do?

Hopefully that is explained correctly, please let me know if I need to explain it differently.

Thanks for any help!

Hi,

I have number of file (~5000) in my folder "C:MyStock". Some of the file names are very long so I am getting a disk error while every time booting my computer.

Can any one of you please provide me with some VBA code to rename the file. I would like to take the first 20 charactor of the exisiting name (if it is more than 20) as new name. Also I do not want to save another copy of the file with the new name. I simply want to rename the existing file itself.

Thanks in Advance

GNaga

The answer given... would be great. If I could find said toolbar to click
off. It is not under View>>Toolbars>>Mail (or email or anything else I can
find)

I am using Excel 2002
"Lee Keller King" wrote:

> I sent a copy of an Excel spreadsheet to my supervisor using "Send To Mail
> Recipient (as Attachment)." Now I have this annoying toolbar with the
> "To..." "CC..." and "Subject..." areas sitting on top of my spreadsheet.
>
> How do I turn this thing off, or hide it?
>
>
> Lee Keller King


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