to fix a .xls file that wasn't created by me.
In order to do that I need to change the path of an External Data that is an access 2003 file (.mdb file).
The only way that I imagine it is possible is to select a cell that is an adress for the query result, than click on properties and change it selecting the new path of file (the path moves depending on the user of the .xls).
I have the following code written but I'm wondering if it's possible to modify this to change the red line to update to the path that the workbook is saved in? Meaning that User1Folder1 would change but [Workbook1.xlsm]Sheet1'E1 would always be the same.
[Code] .......
I have 12 external files (one per month) and need a quick way to pull these into a reporting book. I've used the following code...
Workbooks.Open FileName:= _
"J:ManfinMISNew ReportingMIS2P200301.xls"
Columns("A:B").Select
Selection.Copy
Windows("MainReportingBook.xls").Activate
Sheets("P200301").Select
Range("A1").Select
ActiveSheet.Paste
Windows("P200301.xls").Activate
Call OpenClipboard(0&): Call EmptyClipboard: Call CloseClipboard
ActiveWindow.Close
However with 10000++ rows of data in each file, this is very very slow (approx 5 mins to update all 12 months).
I'd be very grateful if s.o. help me find solution for the following task:
I want to fill certain worksheet with data from external .tsv files. The .tsv files are with a almost a common name (something_date.tsv), located in folders for each month.
What I'm usually doing and want to automate:
- I'm opening the first .tsv file from the monthly folder;
- Creating AutoFilter on the first row;
- Selecting a custom criteria from the AutoFilter;
- Copying the cells matching this AutoFilter criteria;
- Pasting in a predefined worksheet (with AutoFilter on first row);
*All of the copied cells are not being altered in the predefined worksheet, i.e. the first rows of the .tsv file and the worksheet are the same.
- Doing exactly the same with the next file in the monthly folder (but pasting below the already copied cells in the workbook).
- Etc.;
- Etc.;
I build a VBA that search for 445 files in folders on specific path.
The code opens each file, take each time the same 31 rows, filter them and paste in the main data base file and then close the origin file.
So, for 445 files it takes about 6:30 minutes.
I am designing a spreadsheet for work.
each of the people i work with have their own workbook with their name as the title, John Smith.xls.
There are about 20 of us on the team with their own workbook with the same design.
We record information on these weekly and so there are 20 files in the folder for Week 1, 20 in Week 2 etc.
I want to make a master spreadsheet with will collect all of the information on these sheets and list them on a master spreadsheet every week.
So the external data i am linking to is found in a location like
='C:SpreadsheetsWeek26[John Smith.xls]Sheet1'!$A$1
Here is my problem,
I need the 'Week 26' and the 'John Smith' to be changeable and preferabilly linked to a cell using the INDIRECT formula so I can change all references to Week 26, 27, 28 etc and also change the workbook name it is referencing.
I have attempted to do this in the following way
I broke down the pathname to seperate cells
Cell 1: 'C:Spreadsheets
Cell 2: A reference linking to a cell containing value: Week 26
Cell 3: A reference linking to a cell containing value: [John Smith.xls]
Cell 4: Sheet1'!$A$1
I then used the CONCATENATE formula to link these into a single cell as a text string.
I then tried to use INDIRECT to link to the filemane in the CONCATENATE cell.
This did not work.
I need a way to make parts of the filename variable and linked to one cell.
I also need to do this while the other workbooks are closed.
I have found some information on INDIRECT.EXT which as far as I can make out involves installing mods to Excel to make these work, I cannot do this as the spreadsheet will be used across our network and will eventually be used by thousands of users.
I need to remove the external paths in my macro assignments. I only use shapes. (no active x/form controls)
I tried that, but it won't work for me: [URL]
example of an assignment: 'G:10_excel oolshowPPIupdate.xlam'!button_last12 => should be button_last12 after i run the desired macro
I have a little boo-boo I need to fix. I have a customer that has about 100 workbooks. I needed to add some functions to them so I made up a new sheet of forms and buttons as well as a series of new macros to bring into these workbooks. I knew I couldn't import the macros, so I added the module containing those by hand to all workbooks. I then imported a master copy of the new sheet from a master file i use to hold my working macros. The problem is the buttons on the new sheeets in each workbook now references the master workbook for the macro name, even though each workbook has the same macro of the same name in itself.
My question is, can I make a macro that will edit all the buttons on a specific named sheet (maintenance is the sheet name) in a workbook and take out the external reference.
For example, instead of the assigned macro pointing to c:master.xls!macro1 i just want it to call macro1. And yes, I can go into them and do it by hand. Do I want to manually edit 100 workbooks for a dozen buttons each? No. I want to automate it if I can. If anyone can help I would greatly appreciate it. It would save me a bunch of time.
if it is possible to have one xls file load all .xls files that are inside a folder or a folder with subfolders and so on?
View 9 Replies View RelatedOn the first worksheet of my workbook I have a list of file names in cells I11:I27. The filenames have been 'compiled' using the concatenate function, so for example, although cell I11 displays:
Richard 2007-09.xls,
the cell contents are really:
=CONCATENATE(G20," ",L9,"-",J9,".xls")
Using VBA, I want to be able to open each respective file in cells I11:I27, copy the contents into this workbook (sheet=raw), and close it. My problem is that I don't know how to tell excel the filename in VBA, because the cells contents are not really the filename - they are a formula.
I want to link data between two files, which are always in the same position relative to each other, but can be copied to other locations. Here is an example:
D:Job 1SourceJob Info.xls
D:Job 1SlaveClient Form.xls
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: ....
Cell F9 looks at D9. If there is no value, I would like it to return "", if there is a value, I'd like it to finish the formula.
It figures the formula correctly, providing there is a value in D9.
However, the entire D column is referencing cells from another workbook.
So while no value shows up, if you click on an empty cell in D9 it says:
='C:Workbooks[EnterMenu.xls]Enter Menu Items Here'!D5
How can I get Excel to see 'visually' empty cells in the D column as empty when it seems Excel is reading the cell value as a reference path.
However, that diagnoses might be wrong, since if there is a value there, it figures the formula using the value and pays no attention to the reference path.
This is the formula I'm using currently:
=IF(D9=0,"", IF(N(H9),H9/D9,G9/D9))
I have a drawing index in excel sheet as shown below "there are thousands rows but here is only an example":
FILEID
TITLE
brd213962-1-1.pdf
SPECIAL PIPE SUPPORT DRAWING
brd213962-2-1.pdf
SPECIAL PIPE SUPPORT DRAWING
brd213962-3-1.pdf
SPECIAL PIPE SUPPORT DRAWING
brd213962-4-1.pdf
SPECIAL PIPE SUPPORT DRAWING
brd213962-5-1.pdf
SPECIAL PIPE SUPPORT DRAWING
And in the same folder I have all the drawings in PDF format and all the drawing are exactly named as listed under FILEID column.
I just want to click the drawing number in the excel sheet and get the drawing opened automatically.
Currently what I am doing is searching the index for a particular drawing and then searching the folder for that drawing to open.
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?
I have a list of file locations of .pdf files in column A of my spreadsheet. All the path and files have info on Sheet1.
i.e. Column A1 c: estpacking_1.pdf A2 c: estcountry_1.pdf
Can I use VBA to, when I run it, print all of the files in the list in column A1:D20?
I do a search on my directory for all *.xls files each month which produces a list some 11,000+ lines long. Attached is a very small sample.
I then import this into Excel – see tab Raw Data
What I want to produce is a list shown on tab Finished Data, so that I can then sort etc.
Has someone already done this before and got the code, or can someone point me in the right direction on how to arrive at the finished data?
I have the following code which edits an excel file and saves it in a different format, I think want the code to take this newly saved file and open it in an external application (Softplot) and then save it through this new application.
VB:
Sub FormatMacro1a(ws As Worksheet)
ws.Copy
Rows(1).Delete
Columns("D:E").Cut Destination:=Columns("J:K")
Columns("F:K").Cut Destination:=Columns("D:I")
Range("E1:E201").Value = "0"
[Code] .....
As it stands I have the file saving in a new format and I can open a specific file in softplot through VBA however where I am getting stuck is opening my newly saved file and then saving it.
I have tried the following :
VB:
Path = "C:Program FilesSoftPlot-8softplot.exe"
File = "ActiveWorkbook"
I am trying to open excel files in a file path which includes folders which also have excel folders i wish to open there are quite a few.
At the moment i am working with this code but it fails to open excel files which are within the folders in the specified file path. Its fine for excel workbooks in the folder specified by file path.
This is the code
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
[Code].....
I have two pieces of code that each work, but I am struggling to combine the two.
I started with Ron de Bruin's code to attach multiple files to an email and then found more code that will loop through a folder to attach multiple files to an email.
Essentially, I would like the structure of Ron de Bruin's code, with the ability to have folder paths in the cell range, rather than file names.
Here is the code that works to loop through a folder, but it only works when I have the paths listed in one column, not in a range (e.g. columns C-Z)
Code:
Sub Send_Indv_Files()
Dim OutApp As Object
Dim OutMail As Object
[Code].....
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 am trying to put this in about 25 cells to point at 25 different files. Basically I am trying to keep an eye on when the files have been updated each morning so that I can then pull off some data from them and who saved it.
I solved the first part (see next post) but I still havent been able to get it a function pull off the username.
I have a directory where our customer service people deposit forms each day. The form is always the same, with a number of columns that need to be copied into a master list. From there they are analyzed, sorted, etc, then exported, so the number of rows in that list varies from day to day as well. Is there some way to have VBA import each file sequentially from a directory if I do not have the exact file names?
I am using this import code from a macro to start with:
Sub importdata()
' importdata Macro
Range("A2").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=C:Documents and SettingsMy DocumentsTender Document(1).xls;DefaultDir=C:Documents a" _
[Code]....
I have a directory where our customer service people deposit forms each day. The form is always the same, with a number of columns that need to be copied into a master list. From there they are analyzed, sorted, etc, then exported, so the number of rows in that list varies from day to day as well. Is there some way to have VBA import each file sequentially from a directory if I do not have the exact file names?
I am using this import code from a macro to start with: .....
I have my code here:
VB:
Sub openfiles()Dim Path As String
Dim ExcelFile As String
' Path = GetFolder("C:UsersKinteshDesktop")
Path = "C:UsersKinteshDesktopVBA programmingMaps"
ExcelFile = Dir(Path & "*.xls")
[Code] ....
NextCode:
GetFolder = sitem
Set fldr = Nothing
End Function
My problem is that the code all actually works (including the function and when I use the commented part), but pointing to this one specific directory (the one I'm using right now), literally nothing happens.
Is it possible to update external links on a worksheet via running a macro?
I have a worksheet where some cells have external links to other files, and every month I have to scroll through and do the typing in order to have everything up and running.
A typical cell has formulas like =[E:ReportsAug-2007.xls]Data!F25
Another cell =[E:ReportsAug-2007.xls]Data!F43
and so on.
Every new month I actually have to replace all [E:ReportsAug-2007.xls] occurrences with for example [E:ReportsSep-2007.xls]. So, is it possible to have a macro which opens a textbox of which i can type the new month 'Sep', so that when the macro runs I save all the typing?
My Example
I have Master Book1 which has external links to Book2, Book3 and Book4.
My Formula in Cell B2 is ='D: empxl[Book2.xls]Sheet1'!$A$1
In Cells A1, A2 and A3 I have the text entries 2, 3 and 4 respectively.
Is it possible to use these Text entries to update the Formula I plan to copy down from B2 onwards?
i.e. Formula would read something like ='D: empxl[ Book"A1".xls]Sheet1'!$A$1
I have created a PPT which is linked with my excel data and charts. Now I have to copy it at different location (Both PPT and excel), But due to change in path; my PPT is not updating with latest data points and charts. I know, I can change link using edit link.
My PPT is having around 50+ link. some VBA code which can replace old path using the current path.
I am trying to use the same VBA code for different applications using variables.
Basically I want to have a variables sheet that has the different directories based on the macro selection. My variable sourceloc would change based on the variable name on the sheet.
sometimes I would want it to equal what was in a1 and sometime I would want what was in a2
a1 cell contains "f:filezilla files
a2 cell contains "f:dmc files
This is the code I started and it gives compile error when ChDir = sourceloc is reached
Public sourceloc
Sub filezillasettings()
Dim sourceloc As String
sourceloc = ActiveSheet.[a1] ' a1 cell contains the directory name and folder name =
End Sub
I have a master workbook that is referencing data from several other workbooks whose file names are based on the years data was taken. In the master work book on any given sheet, I would like to be able change the data being used in the formula calculations by simply changing the year value in a single cell. By changing the date value, the formulas are directed from one data workbook to another.
e.g. in the master workbook in Sheet1, if you enter 2008 into cell A1, all of the formulas in Sheet1 now pull values from 2008.xls; if 2008 was replaced with 2009 in cell A1 on Sheet1 of the master workbook, then all of the formulas on Sheet1 would now pull values from 2009.xls.As I am completely new to VBA (bought my first book last night), I am looking for any help that is out there. I am not entirely new to programming (used C++ to write numerical simulations) but I have zero experience writing macros and the like.
I have a set up a workbook which is linked to numerous other workbooks to provide a summary of information in one location.
However, when we reach April the names of all the linked worksheets will change from 'name 09 - 10' to 'name 10 - 11'.
What is the best way of handling this transition, will I have to manually change all of the links?
I have attached a much simplified example, unfortunately I cannot post the original due to sensitive data.
Apologies if the explanation isnt clear enough, let me know and I'll give further detail.