Does anyone have a way to make Excel Zip and/or Unzip Files using only Windows Native Functionality?
I know I can use command line zip utilities like Winzip or Winrar. But I'm trying to make some scripts that will be shared among other users, and would like to NOT require users to install extra software to make it work.
I know windows has the built in capability to zip unzip files through the windows GUI, so there must be a way to do it with command lines from VBA code...
P.S. At the moment I am mostly concerned with UNzipping.
I've struggled to find the code to unzip files using VBA but I finally found it and it works (using Shell).
The problem is that I want to unzip all zip files in a folder but my zip files contain 3 files:
1 csv file that I want to keep and 1 xml and 1 txt file that I want to delete.
I can't just unzip everything and deal with the txt and xml later because the xml and txt files all have the same name. So if I don't do anything, I have a series of overwriting messages prompting me to click yes or no. So what I want is either to delete xml and txt one by one after they are created or get rid of the overwriting prompt. I've tried to put a kill or delete file just after the unzip command but it doesn't work - it's telling me that the txt file doesn't exist (should I add some sort of refreshing command ?) and I couldn't find anything on google about overwriting text or xml files.
I'm using:- Open "file name" For Input As #1 to read and manipulate files. However, I've hit a problem with how to open and read zipped files (winrar).
I have code with downloads files from our work intranet site. When the files download the are zipped up. Is there a code which will unzip and open these excel files?
I have an Excel application (Excel 2003) which is stored in the default Program Files folder by the Installer; for example: c:program fileszxchello.xls. The problem I am running into is this file opens as Read Only in Vista and this is interfering with the running of the application. There is no problem opening the file normally in Windows XP.
I have been able to narrow down the cause of this to the User Account Control system in Vista - if I turn OFF User Account Control, the Excel file opens normally and my application functions normally. Is there another option to open the Excel file without turning OFF User Account Control because some users may find it unacceptable to turn OFF this security feature. Ofcourse, one option is to install the application in another location, outside the Program Folder, and the file would open normally, but the Packaging Wizard that I am using to package the application does not allow me to install the application in any other location and thus, the application installs in the Program Folder and I am running into this problem of the Exel file opening as Read Only. Is there a way out of this situation where I can open the file normally (not as Read Only)?
I'm trying to automate unzipping many .gz files on a monthly basis. Previously I used a macro a coworker wrote to do the unzipping using winzip (think he may have used some code snippets from this forum for it - seems like several sites have similar code shown in different postings). But now my office has switched over to pkware's SecureZIP, I can no longer use winzip, and my coworker has moved on. Can I manipulate that old code to work with SecureZIP somehow?
I have a macro that saves zip files automatically from outlook every day and saves over those zip files (which I want). But now I need to unzip those files and save their csv files in a different folder. I will be saving over the same csv files everyday.
(I have 5 zip files saved and named and each file contains just 1 csv file)
I have had a look at Ron de Bruin's code but he requires you to open the and search for the zip file. I would like this to unzip and save automatically without having to use "Application.Getopen".
As I am running the Sun Dreamteam at work and have 25 different teams, is there a program that will work out how many points each team have once the points have been entered.
I have looked but cannot find anything, just wondering if someone out there has set one up that I can use.
When an .xls file is double clicked, Excel opens with the following error message:
'F:Program.xls’ could not be found. Check the spelling of the file name, and verify that the file location is correct. If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted.
When the dialog box is clicked, another error message is displayed with the same words, but with the file name 'microsoft.xls'.
I have tried un-registering and re-registering Excel, and uninstalling and re-installing all of Office 2003.
KB 177248 refers to this problem and suggests: ....
How to call another program from excel using VBA, then excetue commands within it. All the commands are text based, and the program will respond to it, but i am not sure how to automate this.
I would like to know if it is possible to run a macro from excel for another program and then have results imported into excel.
Currently I have a program called Reflection WRQ that I run a macro in and the results are captured in a text file. Then I run a macro in Excel that imports that text file. If possible I would like to have my macro in Excel run the command in Reflection and then import the results in Excel.
I have data in an Excel worksheet that needs to be entered into a proprietary programme. I believe this is possible using SendKeys but as I need to keep switching back to Excel to get the next bit of data I am unsure as to how to go about this. The program would already be open as it is a dial up situation and I would have to dial into the relevant site first.
I am making a Program search tool for my company. I would like the operator to type a part number into a cell. If the part number is valid (from a master list on a different sheet), then I would like to pull the information from the master list and populate a few cells on the search worksheet.
Example:
An operator types: "W3303-01" in a cell and clicks a button. The macro would populate cells on that worksheet with information from another worksheet that pertains to "W3303-01"
i m trying to use Excel program to keep track of the hours he is working. I know there is a way to do it but it has been many years since I used Excel and can not remember how to do it. He would like to insert the time (hours & minutes) he works in column A and have a sum in the next column.
I've just begun a C&G in VB for work even though at work we use Excel, I'm trying to convert the program we did this week so that it works in Excel. Basically I want to click on the Font button and have the font dialogue box come up and let me change the font in the label (if the checkbox is ticked), hope it makes sense, this is what I have but it doesn't work and I cannot work out why.
Now the list is, if the quantity column for the "Productx" is >=1 than whole row will add to the "Order", if not skip it, i have the formula in the "Order" block A2.
i will have about 6-7 list for "product", now in my file is only 2 Product list (Product1, Product2) and now i want to add more for this, like Product3, Product4....... so i think i need to add program for this and also some expert user also tell me need to add VBA for this.
Can I program column B to show a certain value depending on the presence of a string of text in column A? For example, if A1 contains the word "Barbie", then cell B1 shows the value: "Ken"
I am trying to access another program via Excel VBA. The idea is that, we fill out out time and attendance sheets for work on excel workbooks. But, we have to go into a terminal program to enter this information so it can be uploaded to whereever it goes.
I am able to get the Shell command to work. But, it will be riddled with sendkeys commands and application.wait. I am sure that this is not the best way to program something like this. I believe I read that for this to work that it has to be COM compliant. I don't know. The program is a Java based terminal program.
This works for me
Code: ShellExecute 1, "Open", "C:UsersPublic******lib***_core.jar" But this does not
Code: CreateObject "C:UsersPublic******lib***_core.jar" It gives me a Run-time error 429, ActiveX component can't create object.
what excel can process without having the program crash. Our company is using an old dos program to store payroll information (don't ask -they won't change it). Anyway - I have the ability to use pervasive software to pull the information into excel.
The problem here - is that the information is stored line by line. And in order for me to get useful information out of it, I need to sort those lines by date and a task code.
Is it possible to write a VBA macro that passes keystrokes to another program? I have a spreadsheet with over 650 UPC codes for new products that have to be entered into our inventory system. I have downloaded and tried a few macro recorders that record keystrokes and mouse movements, but find them to be unreliable at best.
When I use the macro recorder to automate a task in Excel, (such as moving data from one sheet to another or importing data from a text file) I always go in after I'm done recording and add additional code to bullet-proof the macro (make sure the focus is set properly, etc.). I need to have this kind of control for this task and a macro recorder doesn't give me that (at least not these lower-end shareware programs I have been trying). I know that there are some higher-end macro recorders that include scripting that you can write and edit, but this our "slow" season business-wise and my supervisor has made it clear that he doesn't want to spend any money right now.
My task is simple (just very repetitive, which is why I don't want to spend all day sitting in front of my PC doing this): 1. Open Excel spreadsheet, go to starting data cell 2. Open 3rd party application and go to the appropriate data entry screen 3. Write a macro that does the following: - checks to make sure the sheet is the active sheet - copies contents of the active cell - moves focus to other app and pastes data from active cell - moves focus back to spreadsheet, copies the contents of the cell to the right (using ActiveCell.Offset?) - makes other app active and pastes data - repeat copy/paste for the next cell to right (in spreadsheet) - press twice to save record before returning focus to the spreadsheet - move cursor down one line, repeat entire process until active cell is blank
Is this possible to do? The third party program is not able to listed as a reference in VBA, but I didn't know if it was possible to at least pass keystrokes to it. It is actually a terminal emulator (Anzio Lite). I guess the full-blown version of the program has some scripting capabilities (from the documentation I've read), but the Lite version does not.
How can i use ShellExecute command to start an application. Since the application as to be run in administrator mode, i suppose that i need to pass a Vbyes parameter to the comnmand line. When started manualy, Windows open a secutity box and ask if i approved it.
The application is use for a text capture process name is: Hypersnap6.exe Directory: "c:Program Files (x86)Hypersnap 6"
I did use before the shell() command, but it doesn't work anymore when the targeted application is under administrator mode
I downloaded a workbook at one time and it actually installed the workbook as if it were an actual program on my computer. I would go to Start-All Programs-and it was listed there. I thought this was very cool. Is something like this done through excel/vba or was a third party application used in conjucnction. I know some of Dave's evaluation workbooks are setup this way, but I can't remeber which one.
I have a hyperlink to a PDF file, that when clicked gives me the usual "Some files may contain a virus..." warning, but then gives me a "No program is registered to open this file." message...
I have the latest Adobe Reader installed, and the file opens without problems from any other location (just not from Excel)...
Other hyperlinked file types open fine from within the same worksheet.
PDF is listed in Control Panel > Folder Options > File Types, and is associated to Adobe Reader...
