Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel files update
Hi all,
I'm trying to solve a problem about my files update. I use Bloomberg and BLP function to excel files. My macro runs right but the update is not started, That is, my macro opens files but the update doesn't start or, if it starts, I got I've got the file with "#N/D"... so I don't have files updated. I've tried different ways.. but I got failed. Could somebody look at my code and Give me some advice ?!! Thank a lot to all. ap '----------------------------------------------------------------------- Option Explicit Dim WB As Workbook Dim arrFiles() Dim sFile As String Dim i '----------------------------------------------------------------------- Public Sub Link() Dim sPath As String arrFiles = Array("ALFA", "BETA") sPath = "C:\FILE\" For i = 0 To 1 sFile = Dir(sPath & arrFiles(i) & "*.xls") Workbooks.Open (sPath & arrFiles(i)) Set WB = ActiveWorkbook WB.Save OpFiles Next i End Sub '--------------------------------------------------------------------- Public Sub OpFiles() ' here I use F9 button about the calculation in the sheets ' you can see the command also in optionsą calculations Application.Run "RefireBLP" Application.MaxChange = 0.001 ActiveWorkbook.PrecisionAsDisplayed = False Calculate 'here are rows I inserted by recorded macro about ' the blp for update data Application.Run "RefireBLP" Application.Run "RefireBLP" Range("A5").Select ActiveWindow.SmallScroll ToRight:=8 Application.Run "RefireBLP" ActiveWindow.SmallScroll ToRight:=11 Range("V2").Select Application.Run "RefireBLP" ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Range("A1").Select ActiveWorkbook.Save Application.Run "RefireBLP" ActiveWorkbook.Save Application.Run "RefireBLP" ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 22 ActiveWindow.ScrollColumn = 23 ActiveWindow.ScrollColumn = 24 ActiveWindow.ScrollColumn = 25 ActiveWindow.ScrollColumn = 26 ActiveWindow.ScrollColumn = 27 ActiveWindow.ScrollColumn = 28 ActiveWindow.ScrollColumn = 29 ActiveWindow.ScrollColumn = 30 ActiveWindow.ScrollColumn = 31 ActiveWindow.ScrollColumn = 32 ActiveWindow.ScrollColumn = 33 ActiveWindow.ScrollColumn = 34 ActiveWindow.ScrollColumn = 35 ActiveWindow.ScrollColumn = 36 ActiveWindow.ScrollColumn = 37 ActiveWindow.ScrollColumn = 36 ActiveWindow.ScrollColumn = 35 ActiveWindow.ScrollColumn = 34 ActiveWindow.ScrollColumn = 33 ActiveWindow.ScrollColumn = 32 ActiveWindow.ScrollColumn = 31 ActiveWindow.ScrollColumn = 30 ActiveWindow.ScrollColumn = 29 ActiveWindow.ScrollColumn = 28 ActiveWindow.ScrollColumn = 27 ActiveWindow.ScrollColumn = 26 ActiveWindow.ScrollColumn = 25 ActiveWindow.ScrollColumn = 24 ActiveWindow.ScrollColumn = 23 ActiveWindow.ScrollColumn = 22 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Application.Run "RefireBLP" ActiveWorkbook.Save Application.Run "RefireBLP" Worksheets("Sheet1").Range("AO1:AS38").Calculate Application.Run (sFile & "!" & "DoOnData") Application.Wait Now + (TimeValue("00:00:25")) SaveFiles End Sub '----------------------------------------------------------------------- Public Sub SaveFiles() WB.Save Application.Wait Now + (TimeValue("00:00:15")) WB.Save Application.Wait Now + (TimeValue("00:00:15")) WB.Close savechanges:=True Set WB = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto update excel files | Excel Discussion (Misc queries) | |||
How to open a list of excel files and get update, then close it? | Excel Discussion (Misc queries) | |||
Microsoft Excel does not update my recently used files. | Excel Discussion (Misc queries) | |||
update excel files using a vba script | Excel Programming | |||
How do I update data in 3 related files in Excel? | Excel Discussion (Misc queries) |