We use Excel 2003 and 2007 to track labor data for a field service company. The field tech's complete daily forms and the office staff copy/paste this data into weekly billing worksheets. They use naming conventions to show the jobs and dates in the file name (Daily Labor - Job1 - 031308.xls and Weekly Labor - week ending 3-16.xls. What I would like to do is link the daily data to the weekly forms as they come in. Is there some way to do this when the job names and the dates are always different? Can I tell Excel to expect data files based on the naming conventions as in the Weekly is named "Weekly Labor - Week Ending 3-16.xls so data files named LaborJob1310.csv, laborjob1311.csv would automatically update the weekly sheet? I can put these in a folder and link them, but I don't know how to provide an easy way to do this for the upcoming week.
I have a problem with getting the list of files in subfolders. this code is writing the names of files in a folder to column "A". But it is just "target" folder. But i need to get the name of all files not just under the target folder but also subfolders of target and even subfolders of subfolders of target
VB: Dim FSO As Object Dim SourceFolder As Object Dim FileItem As Object [code]....
Here is a macro I have found and manipulated for my particular case. The problem is that I want to narrow the search criteria by just pdfs and only those starting with Segment which i know in coding would be something like "Segment" & "*" & ".pfd" or something to those terms but the code uses objects which I'm not too familiar with.
Code: Sub ListAllFile()
Dim objFSO As Object Dim objFolder As Object Dim objFile As Object Dim ws As Worksheet
Set objFSO = CreateObject("Scripting.FileSystemObject") Set ws = Sheets("Simple Cut Lists (2)")
I have a folder containing many Excel files. Each is unique, and I wish to characterise them in a separate contents file in Excel. To do so, it would be very useful to populate a worksheet with the names of the files in the folder.
I have a question about creating a custom drop down menu. In AutoCAD, I can create custom drop down menus that loads each time AutoCAD loads and i was wondering if excel have something similar. This menu would be added to the default menu list: "File_Edit_View_Insert_Format_Tools_Data...etc". I have searched online but i keep getting things about how to make drop down menus in cells but nothing about adding a main drop down menu.
What I want to do is create a menu with hyper links to all of the excel files we use quite often here at work.
I have already done this by just creating a sheet with a list of hyper links on it but would like to put it in a drop down menu instead if its possible.
The following code is something that was developed to find files with certain criteria that matches references in cells A1 & A2. The coding is as follows:
Code: Option Explicit Option Compare Text 'for Case-Sensitive matching change Text to Binary Sub List_Matches() Dim sPattern As String, sPath As String, sJob As String Dim sMainDir As String, sCommonSub As String Dim c As Range, lRow As Long
The red is the area of the code i am having problems with. The original code was going to look only in one directory, now I wanted to add a second directory with reference located in E5. Now as is the program works fine looking at directory 1 or E4, but the problem i feel is that for E5 all the folder name are named "WO#_____ - ______". I believe the # sign is not allowing it to open that folder and search for the info. In the code there is a function for solving the # sign problem in a file name, but how could i either modify the code or the function to do the same for the folder name and view the # sign as a valid file name to open?
creating a macro, I have a list of CSV files in a folder called balances, i need a macro that will look into each CSV file and grab all p'folios which have a negative balance in Col D and line them up as per example shown below. The extracted data needs to be copied to file named "Workings" S:RecsalancesWorkings
All data in CSV file looks like this, where header row is row 1 starting at A1
I am unable to install a disk catalog and have been trying with no success to try and get something in excel that will look at a directory and display the contents of that folder with the file attributes etc
Sorry that I'm very new in Excel VBA coder. And, for this topic, I don't even know how to start. I want to make a MS Excel database of a numerous files. This database must be consist of Filename, Location, and it's attribute (let's say updated date, size, hidden status)
Essentially, the Engineering Dept has given me 550 AutoCad files I have dumped into a folder located in this path: C:/DrawingsDump
From these 550 DWG files I need to manually filter and separate 260 drawings that I actually need to use and move them to a folder located in this path: C:/DrawingsFiltered . This manual filtering process takes a lot of time and is tedious work and it has got to be done everytime Engineering changes drawings because they are not kind enough to provide me a delta list.
In Excel 2003 I have setup a list of all 260 drawings I am interested in and that I call my filtered list. Is it possible to create a script that will enable Excel 2003 to compare my filtered list to all of the files inside C:/DrawingsDump, and then copy only those files that match to my Excel Filtered list and then paste only matching files into the C:/DrawingsFiltered folder?
If Excel 2003 can't handle this, is there a third party application that will let me perform a file management function like this with minimum startup time?
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".
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 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.
The situation is that I have created a workbook in a network folder: xprojectsexcelclientAApril umbers
The workbook links to a few other workbooks on the same network, but in the parent folder: xprojectsexcelclientAApril
The problem occurs when my colleagues have to updates the links in the main workbook, because they have not mapped the network drive in the same way as I have. The problem is NOT the prefix, but the fact that they have a more direct link to the network folder. My collages have mapped the folder like: xclientAApril Is there a way where I can make the links in the workbook, so they don't depend on the entire string, but only on the fact that it is in the parent folder?
I have been having problems lately with excel files on Network drives. I have one file that I created on 11/12/2012. However, on Friday that file showed it had been modified by myself on 11/13/2012. I did not do this. As a note, this file contains macros based on a button that is clicked from a worksheet.
I then copied data from this worksheet into another file, creating a link that I did not know was there with the worksheet and the macro. From the first workbook, I also copied in data collection from two other worksheets, also I believe with a link to the first workbook.
I did a test on the first file this morning and found out you can open a file, make no changes, and close the file. Excel will ask you to save the file. If you say Yes, the file will show being modified with today's date and your userid, even though nothing really changed.
What I am actually trying to find out is that can the original file be altered somehow that will automatically update the linked file? Or, when I open the linked file, do I have to say "YES", I want the updates before the linked file is updated?
There were some questions as to how a value showed up in the link file (or actually ended up being removed) and when I looked at the original file it was also removed. We checked on stability across the network drive but found it to be okay, now we are looking at the link issue.
This workbook contains links to other data sources.
*If you update the links, Excel will attempt to retrieve the latest data.
*If you don't update the links, Excel will use the previous information.
Note that data links can be used to access and share confidential information without permission and possibly perform other harmful actions. Do not update the links if you do not trust the source of this workbook.
I've stopped trying to rectify the situation and just click on cancel. So does everyone else. Big mistake. If there is a file that changes from time to time, and you're relying on a link to it to make your data correct, and some one moves or removes it! you get the above message. But you have no idea it's your very important file that has been deleted or moved. And Excel as the message says, uses the last data it had access to.
Is there a way to force Excel to display the #N/A error for the broken link instead of telling the lie? Well really, if the file’s been deleted or moved, Excel has no idea what the true value of the "Link" is and is lying when it puts up that last known value and really doesn't tell you except for that opening message.
Very frustrating, the Bill Of Material was moved and then changed, and my Excel file reported the old value just like that.
I've got a huge file with lots of pages and all sorts of people do lots of things including making links to files that after a while get moved or deleted. I can't chase them all down.
So, is there a way to force Excel to "Crash" and put up an error message in the cell where the link is supposed to be?
Cell B1 in Client Form.xls refers to Cell A1 in Job Info.xls. This link is created when both files are open. When Job Info.xls is closed, the reference in Client Form changes to 'D:Job 1Source[Job Info.xls]Sheet1'!$A$1.
Then, I copy and save Job 1 as Job 2. Thus, the second set of folders are: ....
I am working on a file that has multiple worksheets with many links, lookups and formulas; some between the worksheets and some external to another Excel file. The plan is to use this file as a template and copy it over and over with new names. Once I save the file as a new name in a new folder on my network, will I lose all these links, lookups and formulas? Or is there a way to maintain the links or do a global change of the formulas (i.e., the original file name is "TEMPLATE" and the new file name is "PROJECT!1")?
I have a master workbook (that I will call 'A') with a number of links to other workbooks ('B' to 'Z'). 'A' is designed to collate and display the data held in workbooks 'B' to 'Z'. The links all work fine until the 'B' to 'Z' workbooks are updated with new data, at which point, the links break with a "Error: source not found" error.
This is caused by the process by which we update the 'B' to 'Z' workbooks. We have to delete them, and replace them with new files, that have the same name.
Is there any VBA code or something that I could run that will update the links to pick up the new files?