вопросы по Эксель

[ShamanK]

уважаемые программисты, делаю проект в экселе и поскольку самоучка возникает достаточно много вопросов.

буду тут выкладывать их по мере продвижения самого проекта.
на сегодня есть несколько вопросов:

1
есть урл, надо программно в ячейку занести урл так, чтобы в самой ячейке был виден не адрес в интернете а например - САЙТ
пример
урл http://finance.yahoo.com/q?s=A
в ячейке ссылка с названием - САЙТ, ведущая на урл

2
есть другой урл на картинку в интернете
необходимо
тоже что и в первом вопросе, только при этом
эксель должен в фоновом режиме скачать эту картинку и при наведении мышки на ссылку показывать эту картинку - как пример - так эксель работает с ПРИМЕЧАНИЕм

3
как программно заключить несколько ячеек в ГРАНИЦЫ.
то есть количество заполненных ячеек заранее не известно, программа сама заполняет ячейки и должна все заполненные обвести границей.

пока все.
с Уважением Роман.

[EducatedFool]

1
есть урл, надо программно в ячейку занести урл так, чтобы в самой ячейке был виден не адрес в интернете а например - САЙТ

Есть 2 основных варианта ввода гиперссылки в ячейку:

Sub test1()
    текст = "САЙТ": ссылка = "http://finance.yahoo.com/q?s=A"
    Range("a7").FormulaR1C1 = "=HYPERLINK(""" & ссылка & """,""" & текст & """)"
End Sub

Sub test2()
    текст = "САЙТ": ссылка = "http://finance.yahoo.com/q?s=A"
    Range("a2").Hyperlinks.Add Range("a2"), ссылка, , "Щелкните для перехода на " & ссылка, текст
End Sub

На остальные вопросы отвечу чуть позже - сейчас пока некогда

[ShamanK]

Private Sub CommandButton1_Click()
'старт
Dim sURI As String, htmlcode As String, site As String, dop As String, tf As String, chart As String, i As Integer, wksh1 As Worksheet, wksh2 As Worksheet
Set wksh1 = Worksheets("Glav")
Set wksh2 = Worksheets("Tikers")

site = "http://finance.yahoo.com/q?s="
chart = "http://www.instantcharts.com/c.php?T="
dop = "&B="
i = 1
simv = 72
tf = "30"
wksh1.Cells(1, "A") = "ON"

With wksh1.Cells(1, "A").Interior
.ColorIndex = 4
End With


y = 5
t = 1
Do While wksh2.Cells(t, "A") <> ""
wksh1.Cells(y, "B") = t
With wksh1.Cells(y, "B").Interior
.ColorIndex = 36
End With
wksh1.Cells(y, "C") = Left(wksh2.Cells(t, "A"), InStr(1, wksh2.Cells(t, "A"), " - ") - 1)

With wksh1.Cells(y, "C").Interior
.ColorIndex = 35
End With
(примечание)

y = y + 1
t = t + 1
DoEvents
Loop

End Sub

(примечание)
в ячейку wksh1.Cells(y, "C") записывается тикер к примеру "А"
сайт site = "http://finance.yahoo.com/q?s="
после знака = дописывается тикер, получается ссылка http://finance.yahoo.com/q?s=A

надо чтобы в ячейке wksh1.Cells(y, "C") отображался тикер А в виде ссылки на старницу http://finance.yahoo.com/q?s=A

[EducatedFool]

Не самый оптимальный вариант кода, но работает:

Private Sub CommandButton1_Click()
    Dim sURI As String, htmlcode As String, site As String, dop As String, _
        tf As String, chart As String, i As Integer, wksh1 As Worksheet, wksh2 As Worksheet

    Application.ScreenUpdating = False
    Set wksh1 = Worksheets("Glav"): Set wksh2 = Worksheets("Tikers")

    site = "http://finance.yahoo.com/q?s="
    chart = "http://www.instantcharts.com/c.php?T="
    dop = "&B=": i = 1: simv = 72: tf = "30"

    wksh1.Cells(1, "A") = "ON": wksh1.Cells(1, "A").Interior.ColorIndex = 4


    y = 5
    t = 1
    Do While wksh2.Cells(t, "A") <> ""
        wksh1.Cells(y, "B") = t: wksh1.Cells(y, "B").Interior.ColorIndex = 36

        текст = Split(wksh2.Cells(t, "A"), " - ")(0)
        ссылка = site & текст

        wksh1.Hyperlinks.Add wksh1.Cells(y, "c"), ссылка, , "Щелкните для перехода на " & ссылка, текст
        wksh1.Cells(y, "c").Interior.ColorIndex = 35

        y = y + 1: t = t + 1: DoEvents
    Loop
    Application.ScreenUpdating = True
End Sub

[EducatedFool]

2
есть другой урл на картинку в интернете
необходимо
тоже что и в первом вопросе, только при этом
эксель должен в фоновом режиме скачать эту картинку и при наведении мышки на ссылку показывать эту картинку - как пример - так эксель работает с ПРИМЕЧАНИЕм

Вот весь код:

Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
                                   (ByVal pCaller As Long, ByVal szURL As String, _
                                    ByVal szFileName As String, ByVal dwReserved As Long, _
                                    ByVal lpfnCB As Long) As Long

Function DownLoadFile(FromPathName As String, ToPathName As String) As Boolean
    DownLoadFile = URLDownloadToFile(0, FromPathName, ToPathName, 0, 0) = 0
End Function

Sub Main()
    On Error Resume Next
    Dim sh As Worksheet: Set sh = ActiveSheet
    Dim cell As Range, ra As Range: Application.ScreenUpdating = False
    Set ra = Range([A2], Range("A" & Rows.Count).End(xlUp))
    For Each cell In ra.Cells
        InsertPhoto cell.Hyperlinks(1).Address, cell
    Next cell
End Sub

Sub InsertPhoto(ByVal PhotoPath As String, cell As Range)
    Dim Filename As String
    Filename = Replace(Environ(24), "TEMP=", "") & "\temppic"
    On Error Resume Next: Kill Filename
    cell.AddComment
    If DownLoadFile(PhotoPath, Filename) Then
        cell.Comment.Shape.Fill.UserPicture Filename
    Else
        cell.Comment.Text "Не удалось загрузить картинку..."
    End If
End Sub

Sub DelPics()
    On Error Resume Next
    Dim cell As Range, ra As Range: Application.ScreenUpdating = False
    Set ra = Range([A1], Range("A" & Rows.Count).End(xlUp))
    For Each cell In ra.Cells
        cell.Comment.Delete
    Next cell
    Application.ScreenUpdating = True
End Sub

А вот - пример файла:

[EducatedFool]

3
как программно заключить несколько ячеек в ГРАНИЦЫ.
то есть количество заполненных ячеек заранее не известно, программа сама заполняет ячейки и должна все заполненные обвести границей.

Я в таких случаях использую свою функцию SetRangeBorders:

Sub SetRangeBorders(ByRef ra As Range, ByVal BordersLineStyle As XlLineStyle, ByVal BordersWeight As XlBorderWeight)
    ra.Borders.LineStyle = BordersLineStyle: ra.Borders.Weight = BordersWeight
    ra.Borders(xlDiagonalDown).LineStyle = xlNone: ra.Borders(xlDiagonalUp).LineStyle = xlNone
End Sub

Sub ПримерИспользования()
    Dim ra As Range: Set ra = [a2,b5:b8, d4:g7]    ' определённый диапазон ячеек
    SetRangeBorders ra, xlContinuous, xlThin
End Sub

Sub ПримерИспользования2()
    Dim ra As Range: Set ra = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    ' все заполненные ячейки на листе
    SetRangeBorders ra, xlContinuous, xlThin
End Sub

Sub ПримерИспользования3()
    ' рисуем рамку вокруг каждой 3-й ячейки столбца B
    For i = 1 To 100
        If i Mod 3 = 0 Then SetRangeBorders Cells(i, 2), xlContinuous, xlThick
    Next
End Sub

[ShamanK]

большое спасибо за коды.

еще пара вопросов:

1 - есть файл в интернете Table.csv
адрес на скачивание

http://ichart.finance.yahoo.com/tabl...99&ignore=.csv

как его скачать + переименовать и сохранить?

2 - в данном файле котировки
как его потом открыть с компьютера и в переменные занести данные?

как выглядит файл:
Date,Open,High,Low,Close,Volume,Adj Close
2009-10-16,27.42,27.49,26.94,27.21,3026700, 27.21
2009-10-15,27.49,27.67,27.40,27.62,2827700, 27.62
2009-10-14,27.68,27.73,27.47,27.59,2770600, 27.59
2009-10-13,27.41,27.53,27.15,27.29,1422700, 27.29
2009-10-12,27.59,27.71,27.33,27.46,1895100, 27.46
2009-10-09,27.39,27.54,27.36,27.47,2086300, 27.47
2009-10-08,27.50,27.75,27.31,27.40,3365500, 27.40
2009-10-07,27.07,27.20,26.89,27.10,1589600, 27.10
2009-10-06,27.17,27.47,26.92,27.22,2887400, 27.22
2009-10-05,26.29,26.98,26.21,26.82,3191200, 26.82
2009-10-02,26.41,26.47,25.95,26.31,3574400, 26.31
2009-10-01,26.63,27.32,26.31,26.61,5458600, 26.61
2009-09-30,28.00,28.07,27.51,27.83,4530800, 27.83
2009-09-29,27.80,28.13,27.71,27.90,3125500, 27.90
2009-09-28,27.69,28.23,27.49,27.89,2100100, 27.89
2009-09-25,27.77,27.84,27.48,27.58,3689200, 27.58
2009-09-24,28.38,28.45,27.58,27.83,2975100, 27.83
2009-09-23,28.59,28.83,28.15,28.27,4454800, 28.27
2009-09-22,28.96,28.96,28.39,28.53,4730800, 28.53

[EducatedFool]

1 - есть файл в интернете Table.csv
адрес на скачивание http://ichart.finance.yahoo.com/tabl...99&ignore=.csv
как его скачать + переименовать и сохранить?

Я же давал тебе пример кода...

Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
                                   (ByVal pCaller As Long, ByVal szURL As String, _
                                    ByVal szFileName As String, ByVal dwReserved As Long, _
                                    ByVal lpfnCB As Long) As Long

Function DownLoadFile(FromPathName As String, ToPathName As String) As Boolean
    DownLoadFile = URLDownloadToFile(0, FromPathName, ToPathName, 0, 0) = 0
End Function

Sub Main()
    Dim ссылка As String, Filename As String
    ссылка = "http://ichart.finance.yahoo.com/table.csv?s=A&d=9&e=19&f=2009&g=d&a=10&b=18&c=1999&ignore=.csv"
    Filename = "C:\WINDOWS\Temp\файл.csv"
    On Error Resume Next: Kill Filename

    If DownLoadFile(ссылка, Filename) Then
        Dim wb As Workbook
        Set wb = Workbooks.Open(Filename)
        ' читаем данные из файла
        'wb.Close False
    Else
        MsgBox "Не удалось загрузить файл :(", vbCritical
    End If
End Sub

и в переменные занести данные?

Какие данные тебе нужны?

[ShamanK]

файл

Date,Open,High,Low,Close,Volume,Adj Close
2009-10-16,27.42,27.49,26.94,27.21,3026700, 27.21

столбцы Date и Adj Close не нужны.
остальное
чтобы получилось так:
переменная Open = 27,42
переменная High = 27,49
переменная Low = 26,94
переменная Close = 27,21
переменная Volume = 3026700

эти данные заносятся в переменные.
High - Low - находим внутридневную волатильность.
далее в цикле просчитать строки от 1 до i ( величина задающаяся заранее)
и находим сумму внутридневной волатильности за i дней
далее делим сумму на i и узнаем СРЕДНЮЮ волатильность акции за i дней.

[EducatedFool]

далее в цикле просчитать строки от 1 до i ( величина задающаяся заранее)
и находим сумму внутридневной волатильности за i дней
далее делим сумму на i и узнаем СРЕДНЮЮ волатильность акции за i дней.

Ну и зачем так всё усложнять?
В открытый макросом файл CSV (в последнюю строку нужного столбца) заносим простейшую формулу (это легко делается макросом), и считываем результат вычислений.
И никаких циклов и переменных - всего 2 строки кода...