if it was possible to write a macro that will update a link to a file that has the same file name but now lies in a different folder/directory path. I have about 50 spreadsheets that link to this file which has now been moved.
View 9 RepliesCurrently I am using VBA Code below to Stop Update links prompts
Private Sub Workbook_Open()
Workbooks.Open "C:TempBook2.xls", UpdateLinks:=False
End Sub
Is there code I can use such This Workbook so that I can save this file as another name and it will work? Biz Auto Merged Post;Dear All, I have found VBA code that works.
Private Sub Workbook_Open()
Workbooks.Open ThisWorkbook.Name, UpdateLinks:=False
End Sub
if it would be possible to update all the links in a workbook at once. For example, say I have a worksheet which is linking to a file named something like 4-6-08 (1) and 4-6-08 (2) and so on with the number in brackets being from 1-21. Now say I have the same thing for 4-7 and 4-8 and 4-9. Now I want to change the links to point to 4-10,11,12,13 instead of 6,7,8, and 9. But still there are 21 copies of each sheet. Is there any way to change all the sources at once so all the 4-6's could become 4-10's and 4-7's to 4-11's?
View 12 Replies View RelatedI have several Excel files saved on a network drive at work. We run cost studies and have numerous links mixed in with these files. My question is, when we change data in one sheet, it doesn't update the other sheet where we have it linked. When you click on the address for the link you get, (\fileshareN:Raetc...) If I remove the \fileshare and leave it as N: the links update. What am I doing wrong here? It automatically adds this \fileshare piece when I add the link. When I have removed it and close it, the extra information is added and I am back at square one.
View 2 Replies View RelatedI have a workbook (we'll call it Mainbook)that has links to files on a shared drive (call these subbooks). For some reason, unless the "subbook" files are open, my links in "Mainbook" will not update.
View 9 Replies View RelatedI have a master price list that will have items added or removed from it occasionally. I have sixty workbooks that contain detailed data on mixes of multiple products from the master price list. All the corresponding prices throughout the sixty workbooks are linked to their specific slot on the master price list. The problem: if the sixty workbooks are closed and anyone adds a new row to the master price list, when I open any of the workbooks the values update but the referenced cell does not.
So if the price for Product 25 is located in cell K118, and two products are added in above K118, when I open any of my mix sheets that contain Product 25 the linked cell should be to K120. But it's not doing it that way. It is holding on to the referenced K118 cell and updating the value to the wrong price.
I've played around with the VLOOKUP function but haven't been able to make it work. Linking the cell as I have has worked great, if I could just get it to adjust the reference as new rows are added to the source workbook.
I've figured out that it will update the workbooks when I add a row if all that are required are open when the addition is made. But my boss will not open sixty workbooks every time a change needs to be made to the master price list, which he controls.
My links are not automatically updating on my worksheets. I was recently working on a sheet that linked to a different sheet in the same workbook. I deleted some cells by dragging other cells onto them, but when I went to the sheet that was linked to this sheet, the old information remained. When I clicked the cells that should have said "#Ref", they said =Sheet1!#REf but still had the old information showing until after I hit F9.
View 2 Replies View RelatedIn my worksheet i use a macro to produce my results which runs perfect.
Later i copied my range and pasted as picture link in another sheet.Since then the macro takes a long time to run. I suppose while running the macro xl continuously updates the picture slowing the macro.
i already set calculation as manual but no use.
I have a large workbook 45MB with 40 sheets each containing a section that links to the server to update itself when the workbook calculates. With that being said it's impossible to have the workbook on Automatic calculate because the update process can take about 7-10 minutes so its set to manual. However, I would like to prevent the links from updating (its one link that is used to update the file) by possibly a macro that runs when the workbook opens and prevent the update link from running when I press F9 to manually calculate.
View 2 Replies View RelatedI've got a workbook in which there're many formulas linking to other workbooks. Coz these other workbooks are frequently updated, so the calculation (update) in my workbook is painfully slow and time consuming. I wanna improve this, maybe by VBA or other way instead of using link like this. Anyone can give me some hints or way how to improve this condition???
View 7 Replies View RelatedI and my organization have many files created using Excel 2003. I have now upgraded to Excel 2007 and soon I discovered a huge problem.
- When opening a file using Excel 2007 that was created with Excel 2003 the links in that file is automatically updated even though I get the edit links dialog and choose not to update.
After searching for a solution on the Internet I realize that this is a problem that exist when Excel opens a file created in an earlier version. Microsoft has no solution.
I need to be in control of which links that are updated and not.
Going back to excel 2003 is not an option. Saving the source files in Excel 2007 is not an option.
I'm linking some data from 2 workbooks in the same folder (using excel 2013)
I have a main rota that works great, called "Main Rota.xlsm" I then have a separate basic file called Email Rota.xlsx without all of the bells and whistles of the Main Rota, purley made to email out to staff.
I store both files in the same folder "Site Rota" and it works fine.... until I rename the folder or move the folder to my laptop.
The link in the Email Rota looks like so to start (calling cell A3 from the Main Rota) ='[Main Rota.xlsm]Planning Rota'!A3
As I said it works fine until I move the folder onto my laptop the link then changes to
='D:DesktopSite Rota[Main Rota.xlsm]Planning Rota'!A3
And I have to keep updating the link, I have read online that it isn't supposed to work like this and its supposed to read the file "cleverly" in the same folder ?
I would ideally like to keep both files in the same folder and to update from visa versa without having to update the links if I move it from PC to laptop with I do daily .
Does anyone have any code to put in Workbook_Open module that can get rid of this message that says "The workbook contains automatic links ......." and default the selection to Yes when asked if I want to update all linked information?
I tried this but it doesnt work ...
Private Sub Workbook_Open ()
Application.DisplayAlerts = False
.........
End Sub
I have an unwanted link and I can't remove it. I have tried through the Edit Links dialog box, searched for [ in formulas throughout the workbook and gone searching manually for the reference but to no avail.
View 9 Replies View RelatedI have a 15 excel files which are part of the forecast system. There is another file MASTER which is the host file for the macro. Every months multiple users are asked to update their forecast in the 15 excel files .As these files also display actuals, so they are linked to bunch of other files. The links to the files should not be updated unless I run my macro from MASTER to do the series of tasks.
Everytime these users open the files , they are asked to update links.
Is there a way for me to stop this warning and still able to update the links only when i run my macro from the Master.
I'm working on a dashboard that shows some market developments. One of the problems is that I'm using linked pictures and a combo box to choose the appropriate graph, but sometimes the picture link returns a blank. Apparently this is a bug that can be fixed by running a macro that refreshes the picture link. Unfortunately I'm a real beginner with this kind of stuff.
View 1 Replies View RelatedI have a list of links in 1 excel worksheet. They are spaced every other row if it makes a difference. I need to create a macro that clicks on every link automatically. Each link is a direct file that I need downloaded every few months. I have no Macro experience beyond hitting record to automate basic tasks.
View 13 Replies View RelatedI'm trying to set up a macro so that: in column C I will see the current (old) links being used by the workbookin column D I will input the new links I want to use in the workbookthe macro will open all those files in column Dthe macro will then replace the current (old) links with those listed in column D
I have the following so far, but something doesn't seem to be working - it gets as far as opening the first new file but doesn't open any others and doesn't change any links (although I've tested the part to change links separately and this seemed to work ok).
Sub linked_sheets()
Dim LinkedBooks As Variant
Dim i As Long
LinkedBooks = ThisWorkbook.LinkSources()
For i = LBound(LinkedBooks) To UBound(LinkedBooks)
Cells(i + 3, 3) = LinkedBooks(i)
[Code] ..........
I have a workbook which has links to another excel workbook (source). This workbook has many sheets -all exact copy- with different names. I changed startup prompt (edit->links) to "dont display the alert and dont update automatic links". Then I recorded a macro for updating links (from source) and assigned this macro to a button. What I want to do is when I click this update button I want links to be updated only on the active sheet that Im on. My problem is, when I press this button, it updates all links including other sheets (basically whole workbook).
My current macro is (I dont know coding but I tried changing ActiveWorkbook to ActiveSheet but it didnt work):
Sub update()
ActiveWorkbook.UpdateLink Name:= _
"C:Documents and SettingsAlperDesktopExcelsource.xls", Type _
:=xlExcelLinks
End Sub
I have a macro where I try to edit the links after I open the workbook. I used the macro recorder to get the code, and if I use it, it works great. However, when I try to insert some variables in the code, I get an error saying "Method 'ChangeLink' of object'_Workbook' failed. I don't know why I'm getting this error. Below is code.
Sub MyCode
Dim VarianceMonth As Variant
VarianceMonth = VarianceReport.cboMonth.ListIndex + 1
Windows(VarianceMonth & "_2007 Forecast (LGBU).xls").Activate
ActiveWorkbook.ChangeLink Name:= _
"\sf1d3shareLGBU_ FinanceAdministrative ReportsMonthly Forecasts" & VarianceMonth & "_2007" & VarianceMonth - 1 & "_2007 Forecast (Ancillary).xls" _
, NewName:= _
"\sf1d3shareLGBU_ FinanceAdministrative ReportsMonthly Forecasts" & VarianceMonth & "_2007" & variancmeonth & "_2007 Forecast (Ancillary).xls" _
, Type:=xlExcelLinks
I just started learning VBA and I can't figure out how to create a macro that will update my date range. I need it to automatically add the next date in the next blank cell so it would be like this:
1/1/13 1/2/13 1/3/13 1/4/13
Here is an outline of my work book: GANTT4-1.xlsm
Id like a date to be added if the maximum date in row 6 is exceeded by a date in column k.
I had a spreadsheet with an insert row macro which worked fine and dandy, unfortunatly I decided to split the worksheet across two worksheets and the macro requires updating, The macro currently runs fine for the 'To be State' worksheet but gets an error on the 'As is State' worksheet. The code breaks down on this line:
ActiveCell.EntireRow.Insert Shift:=xlDown
The only difference between the two worksheets that I need is that in the 'To be State worksheet' columns C and O need to be copied down from Row 3 while in the 'As is State' worksheet columns C and U need to be coped down from Row 3.
The worksheet names are:
As is State
To be State
My Code is:
Sub InsertARow()
Dim CurCell As Range
Set CurCell = ActiveCell
Dim CurCellInA As Range
Set CurCellInA = Columns("A").Cells(CurCell.Row)
[Code]...
Here is my scenario:
4 step process or more?
1)I have a text file (with delimiters) that I want to be copied over to an existing workbook (because of the header)
2)So now I have a temporary workbook that includes the header and the data from the text file. I want this temporary data to be transferred or copied to a sheet in another workbook(s), wipe out previous data and store the new one. Save the workbook(s) and close.
3)Close temporary file. Don't save.
4)I want this process to be automated -- run it daily everyday at say, 6:30 in the morning.
I have two workbook.In 'Entry'Workbook where data is filled up daily in different blank cells.In 'Report' workbook,exactly, I want to pull and update the data .The format is same.Then those changes if possible must be shown in a separate sheet.Since the data is very large,I cannot track where the changes has been made in 'Entry' Workbook'.In order to know the changes in cells,I require this on daily basis.Hence I require two things-
1.VBA Code/Macro for pulling & updating changes to 'Report' workbook from 'Entry'Workbook .And only those changes that should be ignored that has been made by me in 'Report'Workbook
2.Changes made in 'Entry'Workbook must be summarised in a separate sheet in 'Report'Workbook.
[URL] ....
So I am trying to make a macro that will update monthly forecast data to what the actual production/consumption data was. The production/consumption numbers come in separate workbooks (i.e. "Jan14", "Feb14") and need to be linked to the 'Book' main file. The path needs to the Jan14 needs to come into the 'Book' file, not just the values. I would like to be able to just have a button above each month that will just automatically load that month's data when it becomes available.
View 1 Replies View RelatedI have a CSV that I have a excel macro created to do some formatting and then export the data into Access. that part works. I also have it checking for duplicate entries and skipping them. The problem that i run into is that not all my duplicates are truly duplicate, there have been updates and now i need to update Access. I have 2 fields, my primary key which is what I'm primarily matching on and then I'm checking if its been updated by checking another field "Last Changed" If this field doesn't match then I need to update the record.
At this point I'm trying to use a SQL UPDATE and I now believe that i have it formatted correct but when it gets to a record that needs to be updated I get an error: Run-time error '-2147217887 (80040e21)': The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. I'm not sure what I'm doing wrong. the only other thing i could think to do would be to check each field to see if it has changed and just run the query against that but I feel like i would run into the same issue.
This is how I'm connecting to Access:
Code:
strTableName = "TableName"
strDBLocation = "xxxxuserhomexxxxxxxxxxxxDatabaseDatabase Backend.accdb"
strConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strDBLocation & ";"
Set cn = New ADODB.Connection
cn.Open strConnect
Set rs = New ADODB.Recordset
rs.Open strTableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable
And this is the Query I'm using:
Code:
strSQL = "UPDATE " & strTableName & " "
strSQL = strSQL & "SET [Work Order ID] = '" & Range("B" & r).Value & "', "
strSQL = strSQL & "[JOBSTATUS] = '" & Range("C" & r).Value & "', "
strSQL = strSQL & "[SUBSTATUS] = '" & Range("D" & r).Value & "', "
strSQL = strSQL & "[Job Status] = '" & Range("E" & r).Value & "', "
strSQL = strSQL & "[STATUSDATETIME] = #" & Range("F" & r).Value & "#, "
[Code] ..........
The SQL code sits in a loop with an IF so it shouldn't execute every time and I plan on once i get this working to revisit this code and try to optimize it but that is not a priority currently.
I'm using both Excel 2007 and Access 2007 on XP.
I have work sheet where certain cells are updating constantly
the cells are D7, D10, D13, D16, D19, D22....... all the way down to D60
Now the values in these cells are changing every few seconds..... what I'd like to do is track the direction the cells are moving in a seperate sheet.
Lets say the cells displayed the following values for example:
D7=2.5
D10=3.4
D13=5.9
D16=8.2
D19=11.6
D22=13.8
D25=16.1
The question is pretty much described in the post title. Here is the situation:
I have a workbook that has data plugged into it manually throughout many sheets, and at the end there are a few sheets with different graphs and charts that are generated by a macro. This macros works perfectly - all the data is accurate and the figures are perfect when compared with a manual count. The problem I have is that the cells where the macro puts the summed values are in a chart that is supposed to create a bar graph. Normally, if I change a value that a bar graph uses I see the bar graph change - but that does not happen with the cells that have values placed in them by the macro. I also have a section that sums the values in the chart to make sure it's running right, and these values do not update automatically as well. The cells have the formula =SUM(B3:B12) and so on, in them. Right now, every time that I run the macro I need to go into the cell with the formula, hit 'enter', and then it does the summation. Is there a way to correct this problem, or is this something that is part of excel?
I have the following macro which runs vlookups between two sheets in excel. Whenever i add columns to the range the vlookup column reference is not the correct cell. Is their any way I can adjust the macro so that the column number adjusts in the formula when a new column is added??
Sub template()
Range("C7").Select
ActiveCell.Formula = "=if(VLOOKUP(C6,'Project master'!B7:BG150,3, FALSE)="""", """",VLOOKUP(C6,'Project master'!B7:BG150,3, FALSE))"
'category
Range("c9").Select
ActiveCell.Formula = "=if(VLOOKUP(C6,'Project master'!B7:BG150,4, FALSE)= """", """",VLOOKUP(C6,'Project master'!B7:BG150,4, FALSE))"
'sub-category
Range("c10").Select...................
This is what I'm trying to do:
1. When a user clicks on a Drop down box and selects "Add New", I would like a Macro to run
2. I want this Macro to open up a Dialog/Text box to say "Enter New Information" where the user can enter in some text
3. The text that is entered by the user into this box will be added as a new entry into the Drop down box selection
My VBA knowledge is still at a beginner's level and I know how to get a Macro to run based on a cell value. However I don't know how to create a Macro for the dialog box nor do I know how to create a Macro for updating the drop-down items.