Rectangle 27 9

Coming from Excel 2003, yes this is possible - you may use the SHDocVw.InternetExplorer and MSHTML.HTMLDocument objects to call a web page, gain control over and interact with the DOM object. After creating references to Microsoft HTML Object Library (...\system32\MSHTML.TLB) and Microsoft Internet Control (...\system32\ieframe.dll) you can play with the following example:

Sub Test()
Dim Browser As SHDocVw.InternetExplorer
Dim HTMLDoc As MSHTML.HTMLDocument

    Set Browser = New SHDocVw.InternetExplorer                     ' create a browser
    Browser.Visible = True                                         ' make it visible
    Application.StatusBar = ".... opening page"
    Browser.navigate "http://www.google.com"                       ' navigate to page
    WaitForBrowser Browser, 10                                     ' wait for completion or timeout

    Application.StatusBar = "gaining control over DOM object"
    Set HTMLDoc = Browser.document                                 ' load the DOM object
    WaitForBrowser Browser, 10

    ' at this point you can start working with the DOM object. Usefull functions are
    ' With HTMLDoc
    '     .getElementByID(string)
    '     .getElementsByTagName(string)
    '     .getElementsByName(string)
    '     .getAttribute(string)
    '     .setAttribute string, string     .... to change field values, click a button etc.
    ' End With

    Application.StatusBar = ""                                      ' time to clean up
    Browser.Quit
    Set HTMLDoc = Nothing
    Set Browser = Nothing
End Sub

Sub WaitForBrowser(Browser As SHDocVw.InternetExplorer, Optional TimeOut As Single = 10)
Dim MyTime As Single

    MyTime = Timer
    Do While Browser.Busy Or (Timer <= MyTime + TimeOut)
        DoEvents
    Loop

    ' if browser still busy after timeout, give up
    If Browser.Busy Then
        MsgBox "I waited for " & Timer - MyTime & " seconds, but browser still busy" & vbCrLf & _
               "I give up now!"
        End
    End If
End Sub

Reading Web Pages using Excel VBA - Stack Overflow

excel vba excel-vba excel-2007 webpage
Rectangle 27 27

I struggled with the same question on a recent project of mine where I wanted to talk to Trello and Salesforce in pure VBA without installing any plugins, opening up Visual Studio, or other hacks. Ended up writing my own library (based heavily on one of my favorites, RestSharp).

Some fun features include Mac support(!), authentication (Http Basic, OAuth1, OAuth2, and others), Async support, and JSON parsing (thanks to VBA-JSON)

It's working awesomely in Excel 2010 and 2013 (and most likely 2007) and I've got it working with Salesforce, Trello, Basecamp, Google Maps, and it should work with pretty much any webservice.

Wow. Great piece of work, have not tested it yet, but looks awesome. Thanks a lot!

calling web service using VBA code in excel 2010 - Stack Overflow

excel vba office-2010 excel-2010
Rectangle 27 8

I don't think the post marked as the answer is right - it links to the Excel 2003 solution, which was based on the MS Office Web Services Toolkit. That solution is no longer valid as the Toolkit is not supported any more. See: MSDN - Consuming Web Services in Excel 2007. The link provides a solution for Excel 2007 which can be transposed to Excel 2010. You will need Visual Studio 2010 with Microsoft Office Developers Tools though. It also means utilising .Net Framework and coding in C# or VB.NET.

Fair enough. In my opinion MS would like to have VBA dead and it is pushing .net solutions for Office. On the seperate note, you don't have to use Visual Studio. You can use NotePad and command line compiler.

One would be surprised about the number of banks (even very large) that would nearly stop working if VBA disappeared tomorrow !!!

Funny, I am working for one of those and they would be very happy with removing any code from an Excel binary file. Some of them have even done that; they have VBA modules stored in a code repository and a workbook is rebuilt from those for each release.

calling web service using VBA code in excel 2010 - Stack Overflow

excel vba office-2010 excel-2010
Rectangle 27 3

FWIW as of the time of posting I've found that the old Web References Toolkit of Excel 2003 still produces working VBA code in Excel 2007/10 (note that there have been some shaky moments with various Windows updates over the last 18 months that has stopped this code working so I wouldn't consider my solution 'reliable').

My horrible hack involves installing Excel 2003, then the Web References Toolkit, then installing Excel 2007 or Excel 2010. I've set up 3 PCs using this hack and all are producing working VBA code to consume asmx web services (I haven't tried connecting with other types of web service but I don't see why they wouldn't work).

The official MS method, using VSTO, is a step too far for some of our clients and this VBA hack has kept them happy.

calling web service using VBA code in excel 2010 - Stack Overflow

excel vba office-2010 excel-2010
Rectangle 27 4

You can use VBA to automate IE (plenty of examples via Google) or you can fetch the page directly using an instance of MSHTTP (ditto plenty of examples on the web). Which is best for your needs will depend on exactly what you want to do. Hard to say more without more detailed requirements.

I did a lot of googling , I did get anything useful. Maybe my sight is the issue, but I won't mind some links from you

Reading Web Pages using Excel VBA - Stack Overflow

excel vba excel-vba excel-2007 webpage
Rectangle 27 3

I am not an expert in VBA , here what I will do 1. Refreshing when opening an Excel file , That can be done by

Sub RefreshData()
    ActiveWorkbook.RefreshAll
' or you can give specific workbook and worksheet identification
End Sub

Private Sub Workbook_Open()
'Call Refresh Data Subroutine when opening file
RefreshData

end sub

You can use worksheet activate event handler if you have multiple worksheet in a file.

Updating MSexcel as soon as server gets update , doesn't make sense as data will be available for view only after file gets open , however if your macro is been used for live monitoring and kept open 24 hours then instead of sending a event from server and catching in VBA , I suggest implement polling every day/hour/minute/second ( call refresh at poll interval) depending upon your refresh requirement criticality.

Application.OnTime TimeToRun, "XMLRefersh"

Refresh XML data from web in Excel - Stack Overflow

xml excel vba web
Rectangle 27 2

IMO, there seems to be a bug/conflict in Excel when opening UTF-8/UTF-8-BOM files using the recorded macro code, specifically when the Origin parameter is set to 65001 which is supposed be UTF-8.

Remove the Origin parameter from the function call and see if the file loads properly Workbooks.OpenText Filename:="C:\file.csv".

If this argument is omitted, the method uses the current setting of the File Origin option in the Text Import Wizard.

I would think that as soon as you link the file with Excel, it should try to read the header of the file and select the correct Country Code automatically (well, assuming the header is not missing).

I have tried different Country Codes and found that in my specific scenario setting Origin:=1252 (1252 - windows-1252 - ANSI Latin 1; Western European (Windows)) loads the file in Excel just fine.

excel - VBA importing UTF-8 CSV file from a web server - Stack Overflo...

excel vba csv utf-8
Rectangle 27 2

IMO, there seems to be a bug/conflict in Excel when opening UTF-8/UTF-8-BOM files using the recorded macro code, specifically when the Origin parameter is set to 65001 which is supposed be UTF-8.

Remove the Origin parameter from the function call and see if the file loads properly Workbooks.OpenText Filename:="C:\file.csv".

If this argument is omitted, the method uses the current setting of the File Origin option in the Text Import Wizard.

I would think that as soon as you link the file with Excel, it should try to read the header of the file and select the correct Country Code automatically (well, assuming the header is not missing).

I have tried different Country Codes and found that in my specific scenario setting Origin:=1252 (1252 - windows-1252 - ANSI Latin 1; Western European (Windows)) loads the file in Excel just fine.

excel - VBA importing UTF-8 CSV file from a web server - Stack Overflo...

excel vba csv utf-8
Rectangle 27 5

Putting it all together

If the characters are displayed correctly when you download the csv file yourself, I'd divide the process to 2 stages:

Sub DownloadFile(ByVal url As String, ByVal local As String)

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", url, False, "username", "password"
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile local, 2 
    oStream.Close
End If

End Sub
Sub OpenCsv(ByVal csvfile As String)
Workbooks.OpenText Filename:= _ 
csvfile,Local:=True,StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False
End Sub

Note That: The Local parameter is the key here,it makes VBA use your excel's local configuration (vietnamese), which is by default set to False.

Sub DownloadAndLoad
  DownloadFile "http://myserver.com/myFile.csv","C:\myFile.csv"
  OpenCsv "C:\myFile.csv"
End Sub

Hi Uri. Thanks for your reply. In my case, the Local:=True doesn't change a thing (is it cause my locals are UK? Changing my locale isn't really an option as I will be processing files of many languages...). For example, the csv file has a in it, when I use the Workbooks.Open the character is still loaded as . I have noticed that when I do this manually (not via macro) the csv is loaded properly. I have recorded a macro from the manual steps but the macro behaviour is different. I have read that csv files may be missing UTF-8 heading - are you familiar with headings at all?

Local should not be used or have an effect when reading UTF-8. @Meehow Yes, putting a UTF-8 BOM in the file fixes it.

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

excel - VBA importing UTF-8 CSV file from a web server - Stack Overflo...

excel vba csv utf-8
Rectangle 27 5

Putting it all together

If the characters are displayed correctly when you download the csv file yourself, I'd divide the process to 2 stages:

Sub DownloadFile(ByVal url As String, ByVal local As String)

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", url, False, "username", "password"
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile local, 2 
    oStream.Close
End If

End Sub
Sub OpenCsv(ByVal csvfile As String)
Workbooks.OpenText Filename:= _ 
csvfile,Local:=True,StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False
End Sub

Note That: The Local parameter is the key here,it makes VBA use your excel's local configuration (vietnamese), which is by default set to False.

Sub DownloadAndLoad
  DownloadFile "http://myserver.com/myFile.csv","C:\myFile.csv"
  OpenCsv "C:\myFile.csv"
End Sub

Hi Uri. Thanks for your reply. In my case, the Local:=True doesn't change a thing (is it cause my locals are UK? Changing my locale isn't really an option as I will be processing files of many languages...). For example, the csv file has a in it, when I use the Workbooks.Open the character is still loaded as . I have noticed that when I do this manually (not via macro) the csv is loaded properly. I have recorded a macro from the manual steps but the macro behaviour is different. I have read that csv files may be missing UTF-8 heading - are you familiar with headings at all?

Local should not be used or have an effect when reading UTF-8. @Meehow Yes, putting a UTF-8 BOM in the file fixes it.

excel - VBA importing UTF-8 CSV file from a web server - Stack Overflo...

excel vba csv utf-8
Rectangle 27 4

I have been looking at a similar problem where we import utf-8 encoded csv files in to a worksheet. I am not pulling the data from a web server but this might help.

My solution is to read the utf-8 file to a local variable then insert it into a sheet. I tried saving the data to a temp file with ansi encoding but doing this caused all the characters to lose their accents.

Function ReadUTF8CSVToSheet(file As String)
    Dim ws As Worksheet
    Dim strText As String

    ' read utf-8 file to strText variable
   With CreateObject("ADODB.Stream")
        .Open
        .Type = 1  ' Private Const adTypeBinary = 1
        .LoadFromFile file
        .Type = 2  ' Private Const adTypeText = 2
        .Charset = "utf-8"
        strText = .ReadText(-1)  ' Private Const adReadAll = -1
    End With

    ' parse strText data to a sheet
    Set ws = Sheets.Add()
    intRow = 1
    For Each strLine In Split(strText, chr(10))
        If strLine <> "" Then
            With ws
                .Cells(intRow, 1) = strLine
                .Cells(intRow, 1).TextToColumns Destination:=Cells(intRow, 1), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                    Semicolon:=False, Comma:=True, Space:=False, Other:=False
            End With

            intRow = intRow + 1
        End If
    Next strLine

    ReadUTF8CSVToSheet = ws.Name

End Function

' to run
strSheetName = ReadUTF8CSVToSheet("C:\temp\utf8file.csv")

excel - VBA importing UTF-8 CSV file from a web server - Stack Overflo...

excel vba csv utf-8
Rectangle 27 4

I have been looking at a similar problem where we import utf-8 encoded csv files in to a worksheet. I am not pulling the data from a web server but this might help.

My solution is to read the utf-8 file to a local variable then insert it into a sheet. I tried saving the data to a temp file with ansi encoding but doing this caused all the characters to lose their accents.

Function ReadUTF8CSVToSheet(file As String)
    Dim ws As Worksheet
    Dim strText As String

    ' read utf-8 file to strText variable
   With CreateObject("ADODB.Stream")
        .Open
        .Type = 1  ' Private Const adTypeBinary = 1
        .LoadFromFile file
        .Type = 2  ' Private Const adTypeText = 2
        .Charset = "utf-8"
        strText = .ReadText(-1)  ' Private Const adReadAll = -1
    End With

    ' parse strText data to a sheet
    Set ws = Sheets.Add()
    intRow = 1
    For Each strLine In Split(strText, chr(10))
        If strLine <> "" Then
            With ws
                .Cells(intRow, 1) = strLine
                .Cells(intRow, 1).TextToColumns Destination:=Cells(intRow, 1), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                    Semicolon:=False, Comma:=True, Space:=False, Other:=False
            End With

            intRow = intRow + 1
        End If
    Next strLine

    ReadUTF8CSVToSheet = ws.Name

End Function

' to run
strSheetName = ReadUTF8CSVToSheet("C:\temp\utf8file.csv")

excel - VBA importing UTF-8 CSV file from a web server - Stack Overflo...

excel vba csv utf-8
Rectangle 27 9

I have found xdocdiff WinMerge Plugin. It is a plugin for WinMerge (both OpenSource and Freeware, you doesn't need to write a VBA nor save an excel to csv or xml). It works just for the celd's contains.

It's useful and need a grid view mode to be more cool

version control - How do I create a readable diff of two spreadsheets ...

git version-control merge diff xls
Rectangle 27 2

According to Excel specifications and limits, worksheets are limited to 1 048 576 rows. You can have as many worksheets as your computer can handle before it exhausts memory.

This file must be loaded into few worksheets. The best approach would be splitting it into eight slices (there are few tools available if you search the web for "split text file Windows" - I have never used any of them) and then load them sequentially into Excel. VBA can you help in second part.

But I'm not sure if that format (one file split into eight sheets) would be suitable for any useful analysis.

Excel is not right tool for this job

excel - Import Csv file with 8 million Records - Stack Overflow

excel csv export-to-excel excel-2013
Rectangle 27 23

I worked on a project that did that (see comment). Unfortunately no code samples from that one, but googling revealed these:

And just in case anyone is wondering: No, this isnt available on reasonably recent Excel versions. :(

Call web service in excel - Stack Overflow

web-services excel
Rectangle 27 1

Text boxes are a form of OfficeArt and these features are not supported with excel web services.

Features that Previously Prevented Excel Files from Loading

In Office SharePoint Server 2007, Excel workbooks that contain unsupported features like VBA macros, form controls, and so on are not loaded in Excel Services. In SharePoint Server 2010, to help users work with this limitation, Excel Services ignores certain unsupported features. In other words, rather than blocking the entire file from loading Excel Services loads the file but you do not see the features that Excel Services does not support. Following are features that do not prevent Excel Services from loading a file:

  • Query tables (also known as external data ranges).
  • Any OfficeArt technology. For example, Shapes, WordArt, SmartArt, organization chart, diagrams, signature lines, ink annotations, and so on.

Note that these features continue to be unsupported. This means that they do not render, execute, or work in any way as they do on the client. Most of the features in the list do not render in Excel Services.

Excel web part for Sharepoint not displaying text boxes - Stack Overfl...

excel sharepoint sharepoint-2010 excel-2013
Rectangle 27 3

In Microsoft Excel Office 2007 try installing "Web Service Reference Tool" plugin. And use the WSDL and add the web-services. And use following code in module to fetch the necessary data from the web-service.

Sub Demo()
    Dim XDoc As MSXML2.DOMDocument
    Dim xEmpDetails As MSXML2.IXMLDOMNode
    Dim xParent As MSXML2.IXMLDOMNode
    Dim xChild As MSXML2.IXMLDOMNode
    Dim query As String
    Dim Col, Row As Integer
    Dim objWS As New clsws_GlobalWeather

    Set XDoc = New MSXML2.DOMDocument
    XDoc.async = False
    XDoc.validateOnParse = False
    query = objWS.wsm_GetCitiesByCountry("india")

    If Not XDoc.LoadXML(query) Then  'strXML is the string with XML'
        Err.Raise XDoc.parseError.ErrorCode, , XDoc.parseError.reason
    End If
    XDoc.LoadXML (query)

    Set xEmpDetails = XDoc.DocumentElement
    Set xParent = xEmpDetails.FirstChild
    Worksheets("Sheet3").Cells(1, 1).Value = "Country"
    Worksheets("Sheet3").Cells(1, 1).Interior.Color = RGB(65, 105, 225)
    Worksheets("Sheet3").Cells(1, 2).Value = "City"
    Worksheets("Sheet3").Cells(1, 2).Interior.Color = RGB(65, 105, 225)
    Row = 2
    Col = 1
    For Each xParent In xEmpDetails.ChildNodes
        For Each xChild In xParent.ChildNodes
            Worksheets("Sheet3").Cells(Row, Col).Value = xChild.Text
            Col = Col + 1
        Next xChild
        Row = Row + 1
        Col = 1
    Next xParent
End Sub

Call web service in excel - Stack Overflow

web-services excel
Rectangle 27 3

You have a few options, actually, depending on what exactly you're trying to do.

xlwt - I think this is great for manipulating Excel files from Python. I've linked you to the documentation. DataNitro - for Python scripting from within Excel. It's a little slow, but I've used it plenty. You are able to (with VBA in general) have Excel make a web request and pull in a table. Before I knew how to program, that was how I did all of my web scraping.

xlrd - Is it possible to push data from Excel sheet using Python into ...

python xlrd xlwt xlutils
Rectangle 27 1

Problem solved, had to call Invoke() on the newSeries - so:

$chart.SeriesCollection().NewSeries.Invoke()

And that's it, I guess there's some difference from calling the VBA in the macro I made in Excel to begin this development:

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("B3:P3")
ActiveChart.SeriesCollection(1).Name = "=""First"""
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "=Sheet2!$B$4:$P$4"
ActiveChart.SeriesCollection(2).Name = "=""Second"""
ActiveChart.SeriesCollection(2).XValues = "=Sheet2!$B$2:$P$2"

To calling it in powershell, I have not found one good example on doing this adding of series dynamically to an Excel Chart on the WEB!

powershell cannot add more than one legend entry (series) to an excel ...

excel powershell charts legend series
Rectangle 27 0

Put your web query on a different page, then pull the data you need into your list on every refresh. Here's an example.

Sub GetPrices()

    Dim rCell As Range
    Dim lIDStart As Long
    Dim qt As QueryTable

    Const sIDTAG = "&ID="

    Application.EnableEvents = False

    Set qt = Sheet1.QueryTables(1)

    'loop through site IDs
    For Each rCell In Sheet2.Range("A2:A3").Cells
        'find the id parameter in the web query connection
        lIDStart = InStr(1, qt.Connection, sIDTAG)

        'if found, change the ID
        If lIDStart > 0 Then
            qt.Connection = Left$(qt.Connection, lIDStart - 1) & sIDTAG & rCell.Value
        Else 'if not found, add the id onto the end
            qt.Connection = qt.Connection & sIDTAG & rCell.Value
        End If

        'refresh the query table
        On Error Resume Next
            qt.Refresh False

            'if the web qeury worked
            If Err.Number = 0 Then
                'write the date
                rCell.Offset(0, 1).Value = Sheet1.Range("A2").Value
                'write the price
                rCell.Offset(0, 2).Value = Sheet1.Range("A4").Value
            Else 'if there was a problem with the query, write an error
                rCell.Offset(0, 1).Value = "Invalid Site"
                rCell.Offset(0, 2).Value = ""
            End If
        On Error GoTo 0
    Next rCell

    Application.EnableEvents = True

End Sub

Thanks for your prompt reply. Good idea of putting the query in other sheet. How about executing the queries for subsequent rows, do the previous query need to be deleted.I have tried your example but giving run time error 9. Where do I need to put the website url in the code. Again thanks for support

You have only one query and you don't need to delete it. The code just changes that one query for each row. On which line do you get the error? My sheets had a CodeName of Sheet1 and Sheet2 so make sure yours match or you change the code. For the URL, create the query manually on the other sheet for the first Site - the URL will be stored in the query and you won't need it in the code.

Hi Dick, have done as you mentionned, got error on second line. would you mind send me your excel sheet.Many thanks

Thanks Dick. My problem is solved. I have reverse the sheet, that's why it was not working, now it is ok. You are great. Many thanks

Excel Dynamic Web query specific data from table and transpose the res...

excel vba