I recently formatted my laptop harddrive. I'm running windows XP Pro. I backed up all files to my 500GB WD external drive. All is fine except that I'm now unable to retrieve my excel files and no one has been able to really help. These spreadsheets are very important to me.
View 9 Replies
I am trying to wite a macro that will allow me to pull up specific files. All files will be located in the same folder. For example, I want to type the workbook name "sawn" into a cell and hit the macro button. The macro will then look in a "pool" folder for the workbook named "sawn" and open it. Is this even possible?
View 14 Replies View RelatedI have several excel files in a folder, and they are named:
AA v.5
AA v.6
AA v.7
AA v.8
BB v.5
BB v.7
CC v.8
CC v.6
The "AA" and "BB" are names of who created it and v.5 are the week it was created.
What I would like to do is in an Excel file be able to choose to retrieve information about for example how man files with v.5 in its name.
OR information about all files with "BB v." in its name (to see all files created bye BB)
Or "BB v.4" (all files created bye BB in week 4)
Or "BB v.4"+"BB v.5"+"CC v.6"
The reason for this is that I would like to be able to create statistics from this so that I can see how it has changed like in a graph. So that I can see the difference and compare how many files BB and CC have created and which week.
I am not able to access *.rpt files in excel macro
By using this below mentioned codes am not able to open the files from folder.
Code:
fpath = "C:Documents and Settings est"
f = Dir(fpath & "*.xls")
Workbooks.Open Filename:=fpath & f
how I would go about addressing this issue. I am given a directory with individual files in it. Each file has critical information I have to extract from it. So, I want the user to provided this directory to the macro and I want the macro to cycle through each file in this directory (excluding super and sub direcories), open it, retrieve info, and close it
View 5 Replies View RelatedI have about 20 files that all have separate passwords. When I enter the password for six of the files, I receive the "Unable to Read File" error message.
View 5 Replies View RelatedMacro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.
Why it stops after one file when using "Application.Run..." to call the macros?
NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.
Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long
[Code]....
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 row of values and I need to pull the second non-zero value on another sheet, which in the example below is $850.
$0 $0 $0 $285 $850 $278 $481
I use the Index/Match forumla below to grab the first non-zero value ($285)
{=+INDEX($A$1:$G$7,MATCH(TRUE,$A$1:$G$7>0,0))}
This formula works great to retrieve the first non-zero value.
How can I modify this formula to grab the second non-zero value ($850)? Then how do I modify it again to grab the third non-zero value ($278)? Then how do I modify to grab the fourth non-zero value ($481)?
I don't want to use IF/Then statement as that might be too long. I think Index/Match would do the trick, but I am missing something.
i have thousands of data for me to look at. its something like this. based on the sample attached file, in Sheet2, in the yellow highlighted cell, i need to display the value based on B1 from Sheet1.
in the file, i want to retrieve c34's value in col B. there are several c34 in col A but all the values belongs to 1 in col B. i want to be able to take in c34's col B value that is, 1 and not the sum. i keep trying sumif formulae but it gives me a 0.
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 need a formula that'll retrieve the sheet name in order to use with lookup function ...
View 11 Replies View RelatedI'm using Excel 2007 and s/s is 325501 rows deep. It consists of a series of approx 30000 ranges between 4 and 30 rows deep.
What I need to do is locate the next appearance of a name and copy its accompanying value to the present occurrence. Doing this manully is not feasible, given the large size of the s/s and I would like to acquire code.
The names are in column B and the values to be retrieved are in the adjoing cell in column C.
The code should only act when there is a number in column S and retrieved values should be placed in column V.
So if XXX appears in B2 and B345 and C345 contains 932, I need 932 to appear in V2.
I want to evaluate a column of Dates to find the min value and max value then create a Subtitle in A2 based on the results. For example:
Column B Contains
1-May-08
2-May-08
5-May-08
15-May-08
From this column I want to produce a subtitle like this for the worksheet in A1 & A2:
Online Report
1-May 2008 to 15-May 2008
I know how to use the =min(B1:B4) formula to get the min result as well the max but I want it coded in a macro. And Also how do I get the result values combined in Cell A2.
The formula I'm looking for should look for the name mentioned in B2 in range AC2:AF400 and retrieve the corresponding data below it starting from D11 to D41 and continue on F7 to F34 etc.
View 3 Replies View RelatedI need to match and retrieve data.
If cell A25 matches B3:B19, B25 should pull the Name and C25 the Cost.
I have attached a spreadsheet.
I have a workbook with 2 sheets containing data in many columns in Sheet1 as well as Sheet2. I need to compare Sheet1 data in column F with data of Column C in Sheet2.
If column F in sheet 1 matching with Column C in Sheet2 then retrieve the value of Sheet2 Column B,C,D & E to show in Sheet1 H, B, C & D respectively. I have attached the Excel workbook.
This code should retrieve data from sql server to excel sheet
filtering according to the "where" statement
its a modified recorded macro.
I can't seem to get my head around the simple issue of assigning a range of cell data to an array! Here is a snippet of what I have, maybe you can see the flaw:
View 8 Replies View RelatedIn the attached file for ODM AND ACCOUNT NAME ACCOUNT PAGE TWO PAGE C1 CELLS MEVCUT.BEN to the 2009/38 VALUE ODM PAGE AVAILABLE ON A TIME I ENTER: THE ENTIRE ROW A SUTUNUNDAKI of the SAME VALUE FILE ACCOUNT INFORMATION In the LISTELEMEK would HEATING
View 4 Replies View RelatedI still do not have my dummy files ready but wanted to start the discussion on Retrieving Specific Data form Txt file.
Later I will post sample files.
For now, I will appreciate any ideas on how can I refer to specific place in the txt file and get the relevant information to excel spreadsheet.
The criteria for searching the text file must be:
1.Look for specific ABC
2.right below must be a date: 01/20/2010
3.When those two criteria are met then go down and find the first occurrence of the word: “Test”
4.When you find the word, on the right of this word must find numbers
5.Get those numbers to excel
I have a custom report on one tab called "Professional Services" and a list of invoices on another tab called "invoices". I need to pull the invoices from the invoice tab and put them on to the Professional Services tab in the appropriate place on the report.
For example, I need to pull back all invoices for center 820202, with the budget line name Controller Team Contractor. I want the Vendor name in column B and the Amount in column H. Here's another catch. If there is more than one "Vendor" with the same name, I want the Amount to sum. Make sense?
I thought maybe putting the invoice list in to a pivot table would be the answer but I can't get the vendor name from the pivot table.
If I have the following in different cells of a worksheet:
ANFKLD
or
AAB
and so on
How do retrieve the second to last letter of any of these strings?
Code:
Customer 1 5,000
Customer 2 10,000
Customer 3 6,000
Customer 4 4,000
Customer 5 3,000
If I have a Table as above, how do I return the Top 3 Customers in 3 cells?
I can get the Values with
=LARGE(Table1[[#All],[Sales]],1)
=LARGE(Table1[[#All],[Sales]],2)
=LARGE(Table1[[#All],[Sales],3)
How would I return the Customer Names?
I have an excel grid which have 2 sheets, sheet2 have all the data, I am doing a macro which based on a value that I put in sheet1 is supposed to find Items which have similar value +/-3 from sheet1. I made a code which I posted below, I don't have any error message but it doesn't work as it should. I explain you with an example: If in the data grid I have the following values
11.8
67.0
25.5
18.1
13.0
if the value I look for is 12 +/-3, it doesn't find anything.
if the value I look for is 11 +/-3, it finds 11.8 but not 13,
if the value I look for is 20 +/-3, it finds 18.1,
if the value I look for is 23 +/-3, it finds 25.5,
Here is my code:
Sheet1.Cells(1, 3).Select
Dim Amin As String, Amax As String ', Bmin As String, Bmax As String
Amin = Sheet1.Cells(3, 2).Value - 3
[Code].....
