PDA

View Full Version : Update value window



mugcy
06-17-2009, 08:37 PM
I'm running a macro to update cells with a Vlookup statement to refer to a particular cell in a WS on a different folder. But I have not yet created the excel file in that folder. I will do that later

Therefore I'm getting an "Update value" window every time the macro copies the formula. is there any code that I can put in so that the window doesn't come up everytime?

Oorang
06-17-2009, 11:43 PM
Hi Mugcy,
Give Application.AskToUpdateLinks a try.

mugcy
06-18-2009, 04:42 PM
Thanks very much Aaron.

I'm using the following code at the moment.
Sub Update()

Dim File As String
Dim Path As String
Dim Cell As Integer
Dim Store As String
Dim sht, psht As Long
Dim filedate As String
Dim day, month As String
Dim startdate, year As String
Dim fdate, fmonth, fyear As Integer
Dim str As String

startdate = InputBox("Enter the Starting Date in dd-mm-yy format", "Start date")
For sht = 1 To 57

Sheets(sht).Select
Store = filename(ActiveSheet.Name) & "'"


fdate = DatePart("d", startdate)
fmonth = DatePart("m", startdate)

day = Formatday(fdate)
month = Formatmonth(fmonth)
year = "09"


Path = "'C:\Documents and Settings\mugcy\Desktop\ArmsUpfiles\"
filedate = day & "-" & month & "-" & year
File = " ARMS Upfile.xls]"
str = "=" & Path & "[" & filedate & File & Store & "!R1C4"
For Cell = 4 To 40
Do While Cell <> 11 And Cell <> 12 And Cell <> 20 And Cell <> 21 And Cell <> 22 And Cell <> 30 And Cell <> 31 And Cell <> 32 And Cell <> 40
If fdate <= 31 Then
Range("B" & Cell).Select
Application.AskToUpdateLinks = False '<===
ActiveCell.FormulaR1C1 = "=" & Path & "[" & filedate & File & Store & "!R1C4"
Range("C" & Cell).Select
ActiveCell.FormulaR1C1 = "=" & Path & "[" & filedate & File & Store & "!R11C4"
Range("D" & Cell).Select
ActiveCell.FormulaR1C1 = "=" & Path & "[" & filedate & File & Store & "!R5C4"
Range("F" & Cell).Select
ActiveCell.FormulaR1C1 = "=" & Path & "[" & filedate & File & Store & "!R9C4"

Cell = Cell + 1

fdate = fdate + 1
day = Formatday(fdate)
filedate = day & "-" & month & "-" & year
Else

fdate = 1
day = Formatday(fdate)
fmonth = fmonth + 1
month = Formatmonth(fmonth)
filedate = day & "-" & month & "-" & year

Range("B" & Cell).Select
ActiveCell.FormulaR1C1 = "=" & Path & "[" & filedate & File & Store & "!R1C4"
Range("C" & Cell).Select
ActiveCell.FormulaR1C1 = "=" & Path & "[" & filedate & File & Store & "!R11C4"
Range("D" & Cell).Select
ActiveCell.FormulaR1C1 = "=" & Path & "[" & filedate & File & Store & "!R5C4"
Range("F" & Cell).Select
ActiveCell.FormulaR1C1 = "=" & Path & "[" & filedate & File & Store & "!R9C4"

Cell = Cell + 1
End If

Loop
Cell = Cell + 1
If Cell = 23 Then
fdate = fdate + 1
day = Formatday(fdate)
filedate = day & "-" & month & "-" & year
Cell = Cell - 1

End If
If Cell = 33 Then
day = Formatday(fdate)
filedate = day & "-" & month & "-" & year
Cell = Cell - 1
End If




Next
Next
End Sub


How can I use Application.AskToUpdateLinks in this context? I tried it here but didn't work.