我真的很抱歉写了这么长的问题,但我想详细解释一下这个问题。我在这里搜索了我的问题,但没有找到一个有效的解决方案,所以在这里发布我的问题。
我有一个Excel文件,其中包含宏,并采取每日开放的兴趣数据从芝加哥商品交易所(芝加哥商品交易所)集团,美国;并更新一个内部数据库,然后更新线图。这个文件现在停止为我工作,我得到了它的编码由一个辉煌的Excel VBA程序员,我现在无法找到解决这个问题。
https://www.cmegroup.com/market-data/volume-open-interest/metals-volume.html
这个网站每天更新两次数据,初步和最终。所以宏有功能,以检查最终数据第一,如果它找到,它更新数据,否则它寻找初步数据和更新它。这个文件有一个工作表称为“主”,我们写在一个单元格的日期,有两个按钮(两个宏,一个更新单个日期的数据,如果多个日期的数据需要更新,则第二个更新数据)。它有一个名为“链接”的工作表,其中包含下载所需工作簿的部分URL/表复制到文件中,然后将特定数据复制到文件中每个工具的单独数据表中,用于制作/更新每个金融工具的未平仓合约折线图。
这些宏对于使用不同Windows和Excel版本的其他朋友来说工作得很好,但它们在我的电脑中停止工作了(是的,在三台笔记本电脑中。几台笔记本电脑有Windows 10和Excel 2016,另一台笔记本电脑有Windows 11和Excel 2021)。奇怪和意外的是,我找到了一个解决方案,当我从工作表“主”按下按钮运行宏时,它卡住了,我按了几次ESC键,宏运行并完美地更新了数据/折线图。但它们对我不起作用,因为它们在完全相同的文件中对其他人起作用。信任中心解除了对宏的阻止并完全启用了宏。
在按下按钮从表“主”,宏启动和文件去无限处理。我复制完整的代码在这里。
Sub download_data2()
Dim lastdate, report_date As Date
Application.ScreenUpdating = False
Sheets("Main").Cells(1, 1).Value = 1
'getting todays links
report_date = Sheets("Main").Cells(1, 3).Value
extr_date = Format(Year(report_date), "00") & Format(Month(report_date), "00") & Format(Day(report_date), "00")
Sheets("Links").Cells(2, 1).Value = extr_date
'links for final data
link_metal = Sheets("Links").Cells(1, 1).Value & extr_date & Sheets("Links").Cells(3, 1).Value & Sheets("Links").Cells(5, 1).Value
link_fx = Sheets("Links").Cells(1, 1).Value & extr_date & Sheets("Links").Cells(4, 1).Value & Sheets("Links").Cells(5, 1).Value
link_oil = Sheets("Links").Cells(1, 1).Value & extr_date & Sheets("Links").Cells(9, 1).Value & Sheets("Links").Cells(5, 1).Value
link_irv = Sheets("Links").Cells(1, 1).Value & extr_date & Sheets("Links").Cells(10, 1).Value & Sheets("Links").Cells(5, 1).Value
link_eqvol = Sheets("Links").Cells(1, 1).Value & extr_date & Sheets("Links").Cells(11, 1).Value & Sheets("Links").Cells(5, 1).Value
wb_name = ActiveWorkbook.Name
Workbooks(wb_name).Sheets("Metals").Range("A1:X5000").Clear
Workbooks(wb_name).Sheets("Metals").Shapes.SelectAll
Selection.Delete
Workbooks(wb_name).Sheets("FX").Range("A1:X5000").Clear
Workbooks(wb_name).Sheets("FX").Shapes.SelectAll
Selection.Delete
Workbooks(wb_name).Sheets("Energy").Range("A1:X5000").Clear
Workbooks(wb_name).Sheets("Energy").Shapes.SelectAll
Selection.Delete
Workbooks(wb_name).Sheets("Interest Rate Volume").Range("A1:X5000").Clear
Workbooks(wb_name).Sheets("Interest Rate Volume").Shapes.SelectAll
Selection.Delete
Workbooks(wb_name).Sheets("Equity Volume").Range("A1:X5000").Clear
Workbooks(wb_name).Sheets("Equity Volume").Shapes.SelectAll
Selection.Delete
'download final data
Application.DisplayAlerts = False
'Application.Wait (Now + 0.000011)
Workbooks.Open link_metal
last_row = Sheets("VOI Totals Report").Cells(5, 1).End(xlDown).Row
last_column = Sheets("VOI Totals Report").Cells(5, 1).End(xlToRight).Column
last_column = Split(Cells(, last_column).Address, "$")(1)
Sheets("VOI Totals Report").Range("A5:" & last_column & last_row).Copy Workbooks(wb_name).Sheets("Metals").Range("A5:" & last_column & last_row)
'Workbooks("voiProductsViewExport").Close False
ActiveWorkbook.Close False
'Application.Wait (Now + 0.000011)
Workbooks.Open link_fx
last_row = Sheets("VOI Totals Report").Cells(5, 1).End(xlDown).Row
last_column = Sheets("VOI Totals Report").Cells(5, 1).End(xlToRight).Column
last_column = Split(Cells(, last_column).Address, "$")(1)
Sheets("VOI Totals Report").Range("A5:" & last_column & last_row).Copy Workbooks(wb_name).Sheets("FX").Range("A5:" & last_column & last_row)
'Workbooks("voiProductsViewExport").Close False
ActiveWorkbook.Close False
'Application.Wait (Now + 0.000011)
Workbooks.Open link_oil
last_row = Sheets("VOI Totals Report").Cells(5, 1).End(xlDown).Row
last_column = Sheets("VOI Totals Report").Cells(5, 1).End(xlToRight).Column
last_column = Split(Cells(, last_column).Address, "$")(1)
Sheets("VOI Totals Report").Range("A5:" & last_column & last_row).Copy Workbooks(wb_name).Sheets("Energy").Range("A5:" & last_column & last_row)
'Workbooks("voiProductsViewExport").Close False
ActiveWorkbook.Close False
'Application.Wait (Now + 0.00011)
Workbooks.Open link_irv
last_row = Sheets("VOI Totals Report").Cells(5, 1).End(xlDown).Row
last_column = Sheets("VOI Totals Report").Cells(5, 1).End(xlToRight).Column
last_column = Split(Cells(, last_column).Address, "$")(1)
Sheets("VOI Totals Report").Range("A5:" & last_column & last_row).Copy Workbooks(wb_name).Sheets("Interest Rate Volume").Range("A5:" & last_column & last_row)
'Workbooks("voiProductsViewExport").Close False
ActiveWorkbook.Close False
'Application.Wait (Now + 0.000011)
Workbooks.Open link_eqvol
last_row = Sheets("VOI Totals Report").Cells(5, 1).End(xlDown).Row
last_column = Sheets("VOI Totals Report").Cells(5, 1).End(xlToRight).Column
last_column = Split(Cells(, last_column).Address, "$")(1)
Sheets("VOI Totals Report").Range("A5:" & last_column & last_row).Copy Workbooks(wb_name).Sheets("Equity Volume").Range("A5:" & last_column & last_row)
'Workbooks("voiProductsViewExport").Close False
ActiveWorkbook.Close False
Application.DisplayAlerts = True
'check if there is final data
If Sheets("Metals").Cells(7, 1).Value = "" Then
'download preliminary data
link_metal = Replace(link_metal, "reportType=F", "reportType=P")
link_fx = Replace(link_fx, "reportType=F", "reportType=P")
link_oil = Replace(link_oil, "reportType=F", "reportType=P")
link_irv = Replace(link_irv, "reportType=F", "reportType=P")
link_eqvol = Replace(link_eqvol, "reportType=F", "reportType=P")
Application.DisplayAlerts = False
'Application.Wait (Now + 0.000011)
Workbooks.Open link_metal
last_row = Sheets("VOI Totals Report").Cells(5, 1).End(xlDown).Row
last_column = Sheets("VOI Totals Report").Cells(5, 1).End(xlToRight).Column
last_column = Split(Cells(, last_column).Address, "$")(1)
Sheets(1).Range("A5:" & last_column & last_row).Copy Workbooks(wb_name).Sheets("Metals").Range("A5:" & last_column & last_row)
'Workbooks("voiProductsViewExport").Close False
ActiveWorkbook.Close False
'Application.Wait (Now + 0.000011)
Workbooks.Open link_fx
last_row = Sheets("VOI Totals Report").Cells(5, 1).End(xlDown).Row
last_column = Sheets("VOI Totals Report").Cells(5, 1).End(xlToRight).Column
last_column = Split(Cells(, last_column).Address, "$")(1)
Sheets("VOI Totals Report").Range("A5:" & last_column & last_row).Copy Workbooks(wb_name).Sheets("FX").Range("A5:" & last_column & last_row)
'Workbooks("voiProductsViewExport").Close False
ActiveWorkbook.Close False
'Application.Wait (Now + 0.000011)
Workbooks.Open link_oil
last_row = Sheets("VOI Totals Report").Cells(5, 1).End(xlDown).Row
last_column = Sheets("VOI Totals Report").Cells(5, 1).End(xlToRight).Column
last_column = Split(Cells(, last_column).Address, "$")(1)
Sheets("VOI Totals Report").Range("A5:" & last_column & last_row).Copy Workbooks(wb_name).Sheets("Energy").Range("A5:" & last_column & last_row)
'Workbooks("voiProductsViewExport").Close False
ActiveWorkbook.Close False
'Application.Wait (Now + 0.000011)
Workbooks.Open link_irv
last_row = Sheets("VOI Totals Report").Cells(5, 1).End(xlDown).Row
last_column = Sheets("VOI Totals Report").Cells(5, 1).End(xlToRight).Column
last_column = Split(Cells(, last_column).Address, "$")(1)
Sheets("VOI Totals Report").Range("A5:" & last_column & last_row).Copy Workbooks(wb_name).Sheets("Interest Rate Volume").Range("A5:" & last_column & last_row)
'Workbooks("voiProductsViewExport").Close False
ActiveWorkbook.Close False
'Application.Wait (Now + 0.000011)
Workbooks.Open link_eqvol
last_row = Sheets("VOI Totals Report").Cells(5, 1).End(xlDown).Row
last_column = Sheets("VOI Totals Report").Cells(5, 1).End(xlToRight).Column
last_column = Split(Cells(, last_column).Address, "$")(1)
Sheets("VOI Totals Report").Range("A5:" & last_column & last_row).Copy Workbooks(wb_name).Sheets("Equity Volume").Range("A5:" & last_column & last_row)
'Workbooks("voiProductsViewExport").Close False
ActiveWorkbook.Close False
Application.DisplayAlerts = True
End If
'check if no data at all for this date
If Sheets("Metals").Cells(7, 1).Value = "" Then
Exit Sub
End If
'updating the tabs
'Gold
For T = 7 To Sheets("Metals").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Metals").Cells(T, 1).Value Like "Gold Futures" Then
met_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_met("Gold", "Gold OI Chart", met_pos, report_date)
met_pos = Empty
'Silver
For T = 7 To Sheets("Metals").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Metals").Cells(T, 1).Value Like "Silver" & "*" & "Future*" Then
met_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_met("Silver", "Silver OI Chart", met_pos, report_date)
met_pos = Empty
'Copper
For T = 7 To Sheets("Metals").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Metals").Cells(T, 1).Value Like "Copper Future*" Then
met_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_met("Copper", "Copper OI Chart", met_pos, report_date)
met_pos = Empty
'Iron Ore
For T = 7 To Sheets("Metals").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Metals").Cells(T, 1).Value Like "Iron Ore" & "*" & "(TSI) Future*" Then
met_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_met("Iron Ore", "Iron Ore OI Chart", met_pos, report_date)
met_pos = Empty
'Palladium
For T = 7 To Sheets("Metals").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Metals").Cells(T, 1).Value Like "Palladium Future*" Then
met_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_met("Palladium", "Palladium OI Chart", met_pos, report_date)
met_pos = Empty
'Platinum
For T = 7 To Sheets("Metals").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Metals").Cells(T, 1).Value Like "Platinum Future*" Then
met_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_met("Platinum", "Platinum OI Chart", met_pos, report_date)
met_pos = Empty
'updating oil
For T = 7 To Sheets("Energy").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Energy").Cells(T, 1).Value Like "Crude Oil Futures" Then
met_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_energy("Oil", "Oil OI Chart", met_pos, report_date)
met_pos = Empty
'Henry Hub Natural Gas
For T = 7 To Sheets("Energy").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Energy").Cells(T, 1).Value Like "Henry Hub Natural Gas Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_energy("Henry Hub Natural Gas", "Henry Hub Natural Gas Chart", fx_pos, report_date)
fx_pos = Empty
'EUR
For T = 7 To Sheets("FX").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("FX").Cells(T, 1).Value Like "Euro FX Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_fx("EUR", "EUR Chart", fx_pos, report_date)
fx_pos = Empty
'GBP
For T = 7 To Sheets("FX").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("FX").Cells(T, 1).Value Like "British Pound Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_fx("GBP", "GBP Chart", fx_pos, report_date)
fx_pos = Empty
'JPY
For T = 7 To Sheets("FX").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("FX").Cells(T, 1).Value Like "Japanese Yen Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_fx("JPY", "JPY Chart", fx_pos, report_date)
fx_pos = Empty
'CHF
For T = 7 To Sheets("FX").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("FX").Cells(T, 1).Value Like "Swiss Franc Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_fx("CHF", "CHF Chart", fx_pos, report_date)
fx_pos = Empty
'AUD
For T = 7 To Sheets("FX").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("FX").Cells(T, 1).Value Like "Australian Dollar Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_fx("AUD", "AUD Chart", fx_pos, report_date)
fx_pos = Empty
'NZD
For T = 7 To Sheets("FX").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("FX").Cells(T, 1).Value Like "New Zealand Dollar Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_fx("NZD", "NZD Chart", fx_pos, report_date)
fx_pos = Empty
'CAD
For T = 7 To Sheets("FX").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("FX").Cells(T, 1).Value Like "Canadian Dollar Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_fx("CAD", "CAD Chart", fx_pos, report_date)
fx_pos = Empty
'10-Year T-Note Future
For T = 7 To Sheets("Interest Rate Volume").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Interest Rate Volume").Cells(T, 1).Value Like "10-Year T-Note Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_irv("10Y TNF", "10Y TNF Chart", fx_pos, report_date)
fx_pos = Empty
'2-Year T-Note Future
For T = 7 To Sheets("Interest Rate Volume").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Interest Rate Volume").Cells(T, 1).Value Like "2-Year T-Note Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_irv("2Y TNF", "2Y TNF Chart", fx_pos, report_date)
fx_pos = Empty
'5-Year T-Note Future
For T = 7 To Sheets("Interest Rate Volume").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Interest Rate Volume").Cells(T, 1).Value Like "5-Year T-Note Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_irv("5Y TNF", "5Y TNF Chart", fx_pos, report_date)
fx_pos = Empty
'Eurodollar Future
For T = 7 To Sheets("Interest Rate Volume").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Interest Rate Volume").Cells(T, 1).Value Like "Eurodollar Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_irv("EURODOLLAR", "EURODOLLAR Chart", fx_pos, report_date)
fx_pos = Empty
'U.S. Treasury Bond Future
For T = 7 To Sheets("Interest Rate Volume").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Interest Rate Volume").Cells(T, 1).Value Like "U.S. Treasury Bond Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_irv("US Treas Bond", "US Treas Bond Chart", fx_pos, report_date)
fx_pos = Empty
'Ultra U.S. Treasury Bond Future
For T = 7 To Sheets("Interest Rate Volume").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Interest Rate Volume").Cells(T, 1).Value Like "Ultra U.S. Treasury Bond Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_irv("Ultra US Treas Bond", "Ultra US Treas Bond Chart", fx_pos, report_date)
fx_pos = Empty
'E-Mini Russell 2000 Index Future
For T = 7 To Sheets("Equity Volume").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Equity Volume").Cells(T, 1).Value Like "E-mini Russell 2000 Index Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_eqvol("E-Mini Rus2000 Index", "E-Mini Rus2000 Index Chart", fx_pos, report_date)
fx_pos = Empty
'E-Mini Dow ($5) Future
For T = 7 To Sheets("Equity Volume").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Equity Volume").Cells(T, 1).Value Like "E-mini Dow ($5) Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_eqvol("E-mini Dow 5", "E-mini Dow 5 Chart", fx_pos, report_date)
fx_pos = Empty
'E-Mini Nasdaq-100 Futures
For T = 7 To Sheets("Equity Volume").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Equity Volume").Cells(T, 1).Value Like "E-mini Nasdaq-100 Futures*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_eqvol("E-mini Nasdaq 100", "E-mini Nasdaq 100 Chart", fx_pos, report_date)
fx_pos = Empty
'E-mini S&P 500 Future
For T = 7 To Sheets("Equity Volume").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Equity Volume").Cells(T, 1).Value Like "E-mini S&P 500 Future*" Then
fx_pos = T
Exit For
End If
Next T
Call Tab_Chart_Update_eqvol("E-mini SP 500", "E-mini SP 500 Chart", fx_pos, report_date)
fx_pos = Empty
' FX update section
' AUDJPY
'For t = 7 To Sheets("FX").Cells(Rows.Count, 1).End(xlUp).Row
'If Sheets("FX").Cells(t, 1).Value Like "Australian Dollar/Japanese Yen Future*" Then
'fx_pos = t
'Exit For
'End If
'Next t
'Call Tab_Chart_Update_fx("AUDJPY", "AUDJPY Chart", fx_pos, report_date)
'fx_pos = Empty
'EURGBP
'For t = 7 To Sheets("FX").Cells(Rows.Count, 1).End(xlUp).Row
'If Sheets("FX").Cells(t, 1).Value Like "Euro/British Pound Future*" Then
'fx_pos = t
'Exit For
'End If
'Next t
'Call Tab_Chart_Update_fx("EURGBP", "EURGBP Chart", fx_pos, report_date)
'fx_pos = Empty
'EURJPY
'For t = 7 To Sheets("FX").Cells(Rows.Count, 1).End(xlUp).Row
'If Sheets("FX").Cells(t, 1).Value Like "Euro/Japanese Yen Future*" Then
'fx_pos = t
'Exit For
'End If
'Next t
'Call Tab_Chart_Update_fx("EURJPY", "EURJPY Chart", fx_pos, report_date)
'fx_pos = Empty
'EURCHF
'For t = 7 To Sheets("FX").Cells(Rows.Count, 1).End(xlUp).Row
'If Sheets("FX").Cells(t, 1).Value Like "Euro/Swiss Franc Future*" Then
'fx_pos = t
'Exit For
'End If
'Next t
'Call Tab_Chart_Update_fx("EURCHF", "EURCHF Chart", fx_pos, report_date)
'fx_pos = Empty
'GBPJPY
'For t = 7 To Sheets("FX").Cells(Rows.Count, 1).End(xlUp).Row
'If Sheets("FX").Cells(t, 1).Value Like "British Pound/Japanese Yen Future*" Then
'fx_pos = t
'Exit For
'End If
'Next t
'Call Tab_Chart_Update_fx("GBPJPY", "GBPJPY Chart", fx_pos, report_date)
'fx_pos = Empty
'AUDNZD
'For t = 7 To Sheets("FX").Cells(Rows.Count, 1).End(xlUp).Row
'If Sheets("FX").Cells(t, 1).Value Like "Australian Dollar/New Zealand Dollar Future*" Then
'fx_pos = t
'Exit For
'End If
'Next t
'Call Tab_Chart_Update_fx("AUDNZD", "AUDNZD Chart", fx_pos, report_date)
'fx_pos = Empty
'EURAUD
'For t = 7 To Sheets("FX").Cells(Rows.Count, 1).End(xlUp).Row
'If Sheets("FX").Cells(t, 1).Value Like "Euro/Australian Dollar Future*" Then
'fx_pos = t
'Exit For
'End If
'Next t
'Call Tab_Chart_Update_fx("EURAUD", "EURAUD Chart", fx_pos, report_date)
'fx_pos = Empty
'EURCAD
'For t = 7 To Sheets("FX").Cells(Rows.Count, 1).End(xlUp).Row
'If Sheets("FX").Cells(t, 1).Value Like "Euro/Canadian Dollar Future*" Then
'fx_pos = t
'Exit For
'End If
'Next t
'Call Tab_Chart_Update_fx("EURCAD", "EURCAD Chart", fx_pos, report_date)
'fx_pos = Empty
Application.ScreenUpdating = True
End Sub
Function Tab_Chart_Update_met(sh_name, ch_name, met_row, report_date)
If met_row = Empty Then Exit Function
lastdate = Sheets(sh_name).Cells(2, 1)
If lastdate = report_date Then
Sheets(sh_name).Cells(2, 4).Value = Sheets("Metals").Cells(met_row, 8).Value
Sheets(sh_name).Cells(2, 3).Value = Sheets("Metals").Cells(met_row, 7).Value
Sheets(sh_name).Cells(2, 2).Value = Sheets("Metals").Cells(met_row, 6).Value
End If
If lastdate < report_date Then
Sheets(sh_name).Cells(2, 4).EntireRow.Insert
Sheets(sh_name).Cells(2, 4).Value = Sheets("Metals").Cells(met_row, 8).Value
Sheets(sh_name).Cells(2, 3).Value = Sheets("Metals").Cells(met_row, 7).Value
Sheets(sh_name).Cells(2, 2).Value = Sheets("Metals").Cells(met_row, 6).Value
Sheets(sh_name).Cells(2, 1).Value = report_date
Sheets(sh_name).Cells(2, 1).NumberFormat = "dd/mm/yy;@"
End If
'Updating the OI charts
For i = 2 To 12
Sheets(ch_name).Cells(i, 1) = Sheets(sh_name).Cells(14 - i, 1)
Sheets(ch_name).Cells(i, 2) = Sheets(sh_name).Cells(14 - i, 3)
Next
End Function
Function Tab_Chart_Update_fx(sh_name, ch_name, fx_row, report_date)
If fx_row = Empty Then Exit Function
lastdate = Sheets(sh_name).Cells(2, 1).Value
If lastdate = report_date Then
Sheets(sh_name).Cells(2, 4).Value = Sheets("FX").Cells(fx_row, 8).Value
Sheets(sh_name).Cells(2, 3).Value = Sheets("FX").Cells(fx_row, 7).Value
Sheets(sh_name).Cells(2, 2).Value = Sheets("FX").Cells(fx_row, 6).Value
End If
If lastdate < report_date Then
Sheets(sh_name).Cells(2, 4).EntireRow.Insert
Sheets(sh_name).Cells(2, 4).Value = Sheets("FX").Cells(fx_row, 8).Value
Sheets(sh_name).Cells(2, 3).Value = Sheets("FX").Cells(fx_row, 7).Value
Sheets(sh_name).Cells(2, 2).Value = Sheets("FX").Cells(fx_row, 6).Value
Sheets(sh_name).Cells(2, 1).Value = report_date
Sheets(sh_name).Cells(2, 1).NumberFormat = "dd/mm/yy;@"
End If
'Updating the OI charts
For i = 2 To 12
Sheets(ch_name).Cells(i, 1) = Sheets(sh_name).Cells(14 - i, 1)
Sheets(ch_name).Cells(i, 2) = Sheets(sh_name).Cells(14 - i, 3)
Next
End Function
Function Tab_Chart_Update_energy(sh_name, ch_name, met_row, report_date)
If met_row = Empty Then Exit Function
lastdate = Sheets(sh_name).Cells(2, 1)
If lastdate = report_date Then
Sheets(sh_name).Cells(2, 4).Value = Sheets("Energy").Cells(met_row, 8).Value
Sheets(sh_name).Cells(2, 3).Value = Sheets("Energy").Cells(met_row, 7).Value
Sheets(sh_name).Cells(2, 2).Value = Sheets("Energy").Cells(met_row, 6).Value
End If
If lastdate < report_date Then
Sheets(sh_name).Cells(2, 4).EntireRow.Insert
Sheets(sh_name).Cells(2, 4).Value = Sheets("Energy").Cells(met_row, 8).Value
Sheets(sh_name).Cells(2, 3).Value = Sheets("Energy").Cells(met_row, 7).Value
Sheets(sh_name).Cells(2, 2).Value = Sheets("Energy").Cells(met_row, 6).Value
Sheets(sh_name).Cells(2, 1).Value = report_date
Sheets(sh_name).Cells(2, 1).NumberFormat = "dd/mm/yy;@"
End If
'Updating the OI charts
For i = 2 To 12
Sheets(ch_name).Cells(i, 1) = Sheets(sh_name).Cells(14 - i, 1)
Sheets(ch_name).Cells(i, 2) = Sheets(sh_name).Cells(14 - i, 3)
Next
End Function
Function Tab_Chart_Update_irv(sh_name, ch_name, met_row, report_date)
If met_row = Empty Then Exit Function
lastdate = Sheets(sh_name).Cells(2, 1)
If lastdate = report_date Then
Sheets(sh_name).Cells(2, 4).Value = Sheets("Interest Rate Volume").Cells(met_row, 8).Value
Sheets(sh_name).Cells(2, 3).Value = Sheets("Interest Rate Volume").Cells(met_row, 7).Value
Sheets(sh_name).Cells(2, 2).Value = Sheets("Interest Rate Volume").Cells(met_row, 6).Value
End If
If lastdate < report_date Then
Sheets(sh_name).Cells(2, 4).EntireRow.Insert
Sheets(sh_name).Cells(2, 4).Value = Sheets("Interest Rate Volume").Cells(met_row, 8).Value
Sheets(sh_name).Cells(2, 3).Value = Sheets("Interest Rate Volume").Cells(met_row, 7).Value
Sheets(sh_name).Cells(2, 2).Value = Sheets("Interest Rate Volume").Cells(met_row, 6).Value
Sheets(sh_name).Cells(2, 1).Value = report_date
Sheets(sh_name).Cells(2, 1).NumberFormat = "dd/mm/yy;@"
End If
'Updating the OI charts
For i = 2 To 12
Sheets(ch_name).Cells(i, 1) = Sheets(sh_name).Cells(14 - i, 1)
Sheets(ch_name).Cells(i, 2) = Sheets(sh_name).Cells(14 - i, 3)
Next
End Function
Function Tab_Chart_Update_eqvol(sh_name, ch_name, met_row, report_date)
If met_row = Empty Then Exit Function
lastdate = Sheets(sh_name).Cells(2, 1)
If lastdate = report_date Then
Sheets(sh_name).Cells(2, 4).Value = Sheets("Equity Volume").Cells(met_row, 8).Value
Sheets(sh_name).Cells(2, 3).Value = Sheets("Equity Volume").Cells(met_row, 7).Value
Sheets(sh_name).Cells(2, 2).Value = Sheets("Equity Volume").Cells(met_row, 6).Value
End If
If lastdate < report_date Then
Sheets(sh_name).Cells(2, 4).EntireRow.Insert
Sheets(sh_name).Cells(2, 4).Value = Sheets("Equity Volume").Cells(met_row, 8).Value
Sheets(sh_name).Cells(2, 3).Value = Sheets("Equity Volume").Cells(met_row, 7).Value
Sheets(sh_name).Cells(2, 2).Value = Sheets("Equity Volume").Cells(met_row, 6).Value
Sheets(sh_name).Cells(2, 1).Value = report_date
Sheets(sh_name).Cells(2, 1).NumberFormat = "dd/mm/yy;@"
End If
'Updating the OI charts
For i = 2 To 12
Sheets(ch_name).Cells(i, 1) = Sheets(sh_name).Cells(14 - i, 1)
Sheets(ch_name).Cells(i, 2) = Sheets(sh_name).Cells(14 - i, 3)
Next
End Function
Sub get_all_up_to_date()
Application.ScreenUpdating = False
Dim startdate, mem_date As Date
Dim day_back As Integer
day_back = Sheets("Links").Cells(15, 1).Value
startdate = Sheets("Gold").Cells(2, 1).Value
If startdate > Date Then
Exit Sub
End If
If Date - startdate > day_back Then startdate = Date - day_back
Do While startdate <= Date - 2
If Weekday(startdate) <> 7 And Weekday(startdate) <> 1 Then
Sheets("Main").Cells(1, 3).Value = startdate
Call download_data2
End If
'MsgBox ("")
startdate = startdate + 1
Loop
Application.ScreenUpdating = True
End Sub
我不是一个开发人员,但可以理解代码的80% - 90%,所以我试图运行宏逐行使用F8键,它卡住了线(工作簿。打开链接_金属)。我找到了一些解决方案
1.打开工作簿前禁用自动安全设置,然后将其重置为原始状态。
1.将Application.EnableEvents=False添加到代码中以停止从同一工作簿的其他函数激发事件处理程序。
1.在ThisWorkBook中复制宏代码,而不是模块或文件
但是似乎什么都不起作用。提前感谢帮助。
1条答案
按热度按时间ufj5ltwl1#
尝试将行
Workbooks.Open link_xxx
替换为OpenWorkbookURL link_xxx
并添加以下子行