i've just started a new job and i'm working shifts and i'm on a 26 week rota that just repeats it self. is their away to put a week rota on to a row that is colured
if i clicked on a comand button i would like it to go into the yellow cells
extract compressed excel file attachement...
i am emailing an excel file as an attachement in outlook 2003 on a windows xp professional...
i would like to compress the file and then send it...
but i don't know how to extract it in vba code on the receiving end...?
I have a folder that contain a list of files in shortcut type. So that i can just click it without going to different drives and paths to open those files and it really save my time.
After updating those files, i need to zip it up. There are many files to zip up and it takes time by going to its path one by one. Do you guys know is there any method to zip up those files without going to its relevant drives and paths?
This Sample is the code that I've adopted for my use.
My problem is this. My normal target .zip files are in the neighborhood of 75 - 80 Mb in size. Yes I know that they are huge..... (They are backup files for mainframe extracts ) Each zip file contains in the neighborhood of 2000 files. Currently, I have coded two ways of extracting data. Well really only 1.
1) I specify a location to extract all files. Which is time consuming ast it takes in the neighborhood of ( 5 min I thing for all files to get extracted ).
2) If I let the user specify a file filter parameter ( part of a file name ) while the WinZip shell is extracting files I'm searching all the file names in the dest directory and deleting the files that I don't want.
So what I want to know is it possible ( without buying software ) to be able to get the filenames of the files in a WinZip file.......
I have on excel file like this :
SKU PRICE QUANTITY
1........25...........4
2.........41..........9
etc......etc........etc
The second one looks like this :
SKU UPC
0...786876
1...44777
etc...etc
While the first file contains about 300 active products the second one has a list of about 2000 active and deactivated products. What I need is to extract the UPC from the second file matching the active SKU from the first file so I have a final file that looks like this:
SKU UPC PRICE QUANTITY
1..44777...25.........4
and so on.
I have around 20 Excelfiles each of them has around 15 Worksheets. Now I would like to extract certain values in order to make calculations and finally to build graphs and diagrams. the value is written next to the descripiton which is common. With copy and paste I get crazy.
How can I automatize it that I can scan Excel files and worksheets for certain values (e.g. total production volume).
I have about 50 - 60 text files and I need to find certain values, namely Name, Account No, total amount under Portfolio Value in the text file and copy these values to a worksheet under column: Name, Account and Portfolio Value respectively. All new entries are paste one row below the existing data.
Some of the text files may not have any data in it or may not have the Portfolio Value as the monthly transactions may occupied 2 or 3 text files. Some text files may have 2 Name in it and there will be 2 Portfolio Values in it which I will need both to be copy to the worksheet.
I have attached both the Excel file and the dummy txt file.
I had a VBA excel question. I have 2 excel files. Each file has a column on product SKUs. What I need is a VBA function that compares the 2 key columns and extracts the matching SKU and saves them in a 3rd excel file. That is the 3 file should contain only the SKUs that are in both of the 2 files.
View 9 Replies View RelatedI have hyperlink on sheet1 from A4:Z500, I want to extract the hyperlink address of .pdf files from them to new sheet in column 'A'.
VB:
Sub ExtractHL_AdjacentCell()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub
The below article explains exactly what I am trying to do but the formula in step 5 doesn't seem to work. I keep getting an #REF! Error.
[URL].....
I am not sure if maybe I'm typing in the quotations wrong or if the formula doesn't actually work.
I have .Raw data in 3 different configurations, which can be opened in Excel.
I want to automatically extract relevant data (Columns) from these 3 different types of .Raw data before running analysis using chart using Macros.
Now here is the problem, Data from the .Rawdata needs to be accumulated. That is, I have multiple sheets of excel, which I have to open manually and extract specific data from individual columns onto another sheet to accumulate every data before running analysis.
How to tackle this situation?
1. Able to detect the right type .Raw data for use in Excel.
2. Extract data from many excel onto one main sheet, using Macros.
Firstly, I have attached a file called test2 which has two sheets within it, one called list and one called 12345. This is for demonstration purposes only as in real life these are both separate files.
What I am trying to do is create a history in the file called list that extracts certain data from the file called 12345 and places it in a more user friendly format. No problems with the formulas etc, just a case of a few ifs and buts.
My real problem lies in the fact that file 12345 is only one of hundreds of files and in order to create the history, I have to repeat the process on all the other files, so that the list grows as I copy the data.
Again for demo purposes, the file "list" sits in the C root ie C:list and the job files sit in C:files*.*.
Is there a way to automate the process so that formulas or VBA residing in the file "list" searches through the C:files*.* and populates the list as it runs through them.
I have a few hundred data/text files to analyze, some a few thousand lines deep. I would like to grab 5, just 5 numbers describing voltage levels. I know the preceding text for each of these in the cell and the following text as well. I just need to delete everything else and keep these 5 values in a column using a script or a macro, and do so for all these files in a specified folder.
This would be 3 weeks of work and the analysis needs to be done ASAP for my research.
Thousands of lines of ancillary data
Thousands of lines of ancillary data
Thousands of lines of ancillary data
Thousands of lines of ancillary data
"11/09/07 14:42:20 J13-50 and J6-9, PCU Resistance (XXXXXXX. Ohms) is within 1.000 to 3741."
Thousands of lines of ancillary data...........................
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
Trying to write a script to carry out a number of tasks but is there code to enable an Excel to look in a particular file for the list of csv files - carry out the tasks and return the appropriate values in the Excel sheet whilst leaving the original csv files untouched?
View 2 Replies View RelatedNeed a way to update a single master file. What happens right now is that the cost manager sends out one file to a lot of different project heads and then on having them return back to him he basically has to individually add in all the data. What i am trying to do is that he somehow carries on sending out the data as there are a lottt of projects and then on returning them they automatically update.
how I can loop through folders to select files starting with a certain word and copy all of them to a different folder and rename them. The folder structure is given below
Company 1(parent folder)
North South East(sub folder) West(sub folder)
Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec
In the above structure, the files are present inside each folders Jan, feb...Dec under the regions North, South East n west. note that I have to select files starting with "Sales" and copy them into a new folder(say results) and rename copied files as Sales1.xls, Sales2.xls etc. (Files are not present in the folder company1, north, south, east and east.)
I want to allow users to place files (.jpg, .tif, .pdf, .pps etc) into a network folder and then have my program open each file and display it for 30 seconds then close the file and the application before opening the next file.
1. Set up a loop to get a directory listing of the folder and write the listing to a text file.
2. Open up the text file and get the next filename in it
3. Open the file in the associated application
4. Wait for 30 seconds (or some period of time) and then close the file
5. Repeat steps 2-4
DoIt = 1
While DoIt = 1
Open "C:TempList.txt" for output as #1
Print #1, Files In Folder
Close #1
Open "C:TempList.txt" for input as #2
while not eof(2)
Line input #2, MyFile
Display MyFile on screen
Wait for 30 seconds
Close MyFile and MyApplication that opened it
wend
Close #2
I can get the directory listing just fine with no problems
I can open the files in the associated application just fine with no problems.
with closing the application after 30 seconds or some period of time.
I need some code that will allow me to easily send it a filename and it will know how to close the file and the application that opened it.
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 using the following code to import thousands of html files into my spreadsheet. The code is working fine. Since I am importing thousands of files, when there is no more space on my worksheet, the code stops with an error message. I want to make this code add another worksheet & continue importing the html files until there are no more files to import.
Sub Master_Importer()
Dim I As Long
Dim strFilename As String
Dim strPath As String
strPath = "file:///C:/Documents and Settings/c/Desktop/New Folder/"
With Application.FileSearch
.LookIn = "C:Documents and SettingscDesktopNew Folder"
.FileType = msoFileTypeAllFiles
.Execute
For I = 1 To .FoundFiles.Count
strFilename = Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "") + 1)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & strPath & strFilename _ .......................
I have a excel file that I enter information into. I have code that saves the files to a certain folder with the name, date, and time stamp for the file name. At the end of the day I might have 3 to 15 excel files I have created that day and I would like to take information from certain cells (examle: L3, B6, B7, B8, G8, and so on) and create a txt file with all of the information in it.
Example:
12/20/2007
Your Name
123 Somewhere St.
Here, OH 45111
Home Visit
12/20/2007
Someone Else
345 Anywhere St.
There, OH 45211
Hospital Visit
I is it possible to convert all comma separated text files in a single folder in to excel files. But the requirement is to have 2 sheets in each new file. first to be the full file - with all columns, and in the second sheet to keep only colum A B D G H K L M O P R S T V W from the first sheet. The second sheet name must be the same as the first one but without the first "wlist_" in the name.
One more thing. The third column in the second is called "COUL". there are short letters for colors in french
can they be converted with the sort in English like it goes:
NO = B
BA = W
RG = R
SO = P
JA = Y
BE = L
VE = GY
GR = G
VI = V
MA = BR
BJ = TA
OR = O
Here is a link to the both CSV and an example excel file with the end result. In this example i haven`t change the shorts for the colors. It takes me too much time with the find and replace function. And at the moment i`m really pushed from time.
[URL]
I have 50 files each with 1000 numbers in column A.
I need to compare every list with every other list and calculate the Pearson function.
I am ok with the vb code to compare every file with everyother.
I can do this by opening each file then closing but it takes too long.
Each of the 50 csv file names is in my destination workbook
I would like to define an array using the file name, then extract 2 lists without opening the files then perform the pearson function and place the value in the destination workbook. (The pearson function just measures the strength of correlation between 2 sets of numbers)
I have a large number of .txt files that are comma separated files (but not saved with the .csv extension) that I need to convert to files with the .csv extension.
Is there an easy way to do this for all files in a given folder? Ideally, the files don't even need to be opened as they are are quite large in size (70MB+) and there are a lot of them (500+).
I have about 100 Excel files in one folder that need to be saved as text files. They can keep the same name, but simply need to be converted to text files. I'd like to use VBA for this and I can't find examples that do exactly that...or ones that my limited knowledge can handle.
Excel files exist in C:Source and ALL of them should be saved as text files in C:Destination. Maybe there is an easier way, but I thought for sure there was a routine I could use.
I have 22 CSV files. I want to search each of those files for a specific text string (ie: "profile" or "description"). When the macro finds the word in any one of the CSV files I want it to copy that complete csv worksheet and paste it into an excel xls file.
how I can go about doing this. Not also that while vlookup is possible, its very time consuming if i have to do this 50 times over and each time the csv files have different file names. that is why i want to search within a file (each file has its own unique identifying text string)
I have the code below. It openes all csv files within a folder, then delete rows depending on the value of a cell. Now it needs to save the file as a xls file ....
View 9 Replies View RelatedI would like to be able to execute a command line that will convert an Excel csv file to an Excel file that is TAB delimited. In other words, replace the comma delimiter with a TAB delimiter without having to open the file in Excel. I am an inexperienced Excel user
View 2 Replies View RelatedIs there a code that will search a pre-determined folder for all .xls files containing the word "Temp", and deleting those files?
Example:
In the root of drive M, I have several Excel files. Anytime one of the original tracking logs is opened, a temp file of that log is automatically created. I would like to automatically search the drive and delete all files containin the word "Temp".
I've attached a picture of the directory tree, in case that will help. The file will always be named "PO Response Tracking - Temp#######.xls", with ###### representing a series of either 5 or 6 numbers.
Attached is a spreadsheet with values contained in it.
We have in column B the date time values at 30min intervals ie 11/12/2013 16:30:00, 11/12/2013 16:00:00, 11/12/2013 15:30:00 etc
We have in column C the value for each 30min time is there ie 6507, 6517, 6531 etc
In column E i would like the 16:30 value for each day
get430dailyvalue.xls
