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'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 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".
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'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 am trying to figure out the calculation steps that Goal Seek is doing behind the scenes so I can create the formula rather than clicking on goal seek whenever I need to make a change. I need to maintain certain weeks of supply every week depending on the sales. Let's say i want to maintain 5 weeks of supply every week, I would like to insert a column for "Suggested To Order' column that would change if weeks of supply goes below 5. In this scenario I would do a Goal Seek, but I want to automate the calculation so I don't have to do this step every week.
I create a schedule based on abbreviations which is given to staff. Then this is retyped up with actual times and put in a schedule book that is the final product. I am trying to automate this change from one sheet to another
n = night shift = 2330-0800 D = day off etc, etc
I have alot more abbreviations, but I hope I just need some advice and can figure it out from there....................
I have a workbook that I use for my purchase orders which is pretty simple. Each row is a separate PO, with 10 columns (po#, description, cost, date, etc.).
Is there a way to automatically generate new workbooks that contain all of the info from each individual row? For example: if I have 100 rows on my spreadsheet, which means I've issued 100 POs, then I want to have 100 individual xls workbooks, with the filename for each workbook representing the PO #s (206904.xls, 206905.xls, etc.).
Dont know if this is possible. I have a spreadsheet where cell A1 contains a value which is not fixed and can change daily, the value can increase and decrease. At present at each month-end I manually record the position of cell A1 at month end in cells C1 through C12 repsenting the 12 months of the year.
Example: A1 @ 31/01/07 = 50 therefore cell C1 = 50 A1 @ 28/02/07 = 45 therefore cell C2 = 45 and so on for 12 months
what I would like to do is automate this process to automatically capture the value at month end into cells "c" and once captured, the figure to remain absolute for that cell/month and to ignore changes in Cell A1 that may subsequently occur.
I have a VBA Macro that starts with a user form that gives me two options. I always need the same option selected, so is there a way to automate this? I want it to select Option B and click "OK" automatically. I don't want to get rid of the user form completely in the background because it has some other functions that need to remain intact.
Is it possible to set up 2 tables whereby if you change a field in 1 table it automatically cuts and pastes a row of info in to another table. Or possibly by clicking a macro hot button? E.g. An entry in 1 table is no longer 'live' so moves to the 'archived' table when a field is changed.
When I save an Excel workbook, I would like to automate it to also save as a web page in 3 different locations (and automatically close after save as a web page). I would like to be able to automate the save as a web page in the 3 different locations and close these web pages. But my Excel workbook (which I have saved as an .xls) I will close manually.
One location to save the web page would be on my desktop and the other 2 locations would be on different shared drives. Can this be done with ******* event or something?
My goal is to automate an excel userfrom. So from outside of excel, i would look to be able to interact with the controls (simply fill in a textbox for example). I'm thinking this may be possible using windows API (user32) and system hooks, but not entirely sure where to start.
I saw this previous thread, that suggests it may not be possible :
i have 110 different columns. i want to do the same thing to each of these formulas (ie i want to calculate things like ln (A2 / A1)
is there some way i can automate this process so i only nhave to type in my formulas once and it will automatically enter in the same formulas for the other columns?
I work in the accounting department. We have an excel workbook for each account with sheets for every month. These sheets contain a template with pre-formulated cells.
For example, the 2010 workbook for account XYZ will have sheets named - Jan 10, Feb 10, Mar 10 and so on.
What we do, is move and copy the previous month sheet, and rename it to the current month.
But each person is responsible for over 100 accounts, so it becomes rather tedious. So I wrote a code which would automate this process.......
I have Adobe 6 and would like to automate the printing of Worksheets to PDFs using coding.
I want to control the location of the output i.e. specific directory. I can do this manually by selecting File...Print Select the Adobe PDF printer....go into options and deselect view PDF results. On printing I then get asked to soecify a location and all works fine....but how can I do this in code.
Unfortunately it also appears that when you restart Excel, the printer properties for the Adobe PDF printer are reset.
How can I code in the change in properties so that I can fully automise this task?
I was gonna explain it but I couldn't make myself clear so I took a screenshot:
sequence.PNG
folder_type is what the user types, (for example, the letter "A"), and folder_code is a sequence that should auto-generate according to what was manually inserted in the folder_type field.
AUTOMATE TRANSPOSE 2-13-14.xlsx In the attached file, I am looking to automate the transposing of the date and numbers under each bold number. Data is truck # in bold, the engine oil change date and mileage below. I copied the data from a pivot and need the date and mileage in columns, date on top with mileage below. I can do it with paste special one truck at a time, the big chunk of data is about 2000 rows deep and was hoping the transpose paste special could be automated, I've made a few attempts on how to do it but can't get it.
where to start with automating a monthly report that I put together.
My report is generated from two or three tables of data - roughly 25,000 records.
Currently, I copy and paste this table of data into one worksheet, and then have pivot tables that are written to take data from this table (this is a typical marketing sales funnel - calls, raw leads, qualified leads, customers, revenue). Then I have another worksheet that is the display to the client, with the proper formatting, mathematical calculations, totals etc. On this client facing worksheet, I pull data from the pivot table (using =GETPIVOTDATA formulas).
It seems that I have constructed this report in a very inefficient way because I have a ton of manual work to do every month to make the client view presentable as changes come about in the raw data. For instance I segregate the client facing report by lead sources, but when new lead sources get added in month by month, I have to edit the client facing report at length.
where to start, or what steps to make this more automated
I have a spreadsheet that I need one thing automated on it (for now). It is a tracking sheet for our QA coordinators. What happens is they have a list of medical charts that are incomplete, when they mark the column complete with an "x" then the cell that contains and "x" under the Team Leaders name should disappear.
I have a code similar but I don't know who to use and array in the code.
This is what I used to change the date of a fields automatically (I recieved this from someone on line here)
I have multiple files that I need to get data from. My dream is to push a button and gather the data from variable files. (I know how to make the button and get data from single file).
The file name is below:
Cell A1 contains this formula ='C:ReportsYear 2009[CityWaterReports Jan 2009.xls]PHFlows'!$B$44
Cell A2 contains this formula ='C:ReportsYear 2009[CityWaterReports Feb 2009.xls]PHFlows'!$B$44 and so on.
I would like the customer to be able to push the button and be asked to enter the year(eg: 2008) and have the formula update to the new year in each cell.
I have developed an application (all written in Excel) that can do comparisons between up to 5 scenarios (A.... E, with A being the Master that the others are compared to. Any one can be designated “A”.
All scenarios are the same format, and all have an identical Transfer Worksheet, to enable the transfer of data from the others to A for comparison to A. So, for A the transfer worksheet becomes an Importer, wherein the user pastes-special – link into array, always B21:E35, one column at a time, for each of up to 4 scenarios B…E.
For the others, Transfer worksheet becomes an Exporter, available for the user to copy the data from each of scenarios B….E, always cells B41:B54....
I have a workbook that starts blank everytime time its opened, and at the push of a button launches a macro that imports some data, sorts and formats it. All good so far.
I am able to create a pivot table (using the wizard) to analyse the data I am left with and display the desired results, but I would like to be able to automate this process, so that the table 'appears' when the original macro has done its work and sorted the data.
I have tried (cheating?) recording a macro of the pivot table wizard, but it fails to run and I am not capable of fixing the programing. Is there an easy way to automate the pivot table process or a macro that can be written that will work?
I have a document that is exported from an SQL database. Once I have the data I need to throw it into a PivotTable to analyze it. However, the export doesn't fill every cell with the information - instead displaying the cell value once until a new value comes up. Therein, there are often a number of blank cells underneath each entry.
I can overcome this by going into the document and autofilling each cell that doesn't have the information in it. However, some of the reports are very large and it is quite time consuming.
What I'm after is for a bit of VBA that will autofill the cells until it hits another new cell value then autofill this down until it hits another new cell value, etc.
I have attached a dummy copy of the report - one sheet, Raw Data, shows the data as it is exported and the other, Autofilled, shows how I would like the data to be displayed after the code is run.
Basically I have set up a list on sheet1, this is now on a drop down in sheet2 and my raw data in sheet3.
I want the table in sheet2 to just display the product items I select in the dropdown but it is displaying all the raw data (although is updating when I update the raw data)
Granted I copied the code whilst researching but I'm just not quite there.
Could someone possibly look at the attached and let me know where I am going wrong. Also as you can tell I am very new to teh VB application in Excel, could anyone reccommend a good place to start learning the very basics?
