Designing web pages for Excel Web Queries

Programming and Web Development Forums - MS EXCEL - Post any VBA, Macros, Add-Ins,Worksheet Functions; Excel Charting questions here

Skip to content

Designing web pages for Excel Web Queries

MS EXCEL
Post a reply

    Next

  • 1. How to count number of errors?
    In simple words what type of a code would be required for: ... On Error Counter=Counter+1; Resume Next Or Goto 0 ... In the end: Msgbox "Number of errors were " & Counter -- Best Regards, Faraz
  • 2. Problem with retaining numbers formatted as text after code ru
    Hi again Julie, Did you have the number format in the correct place in the code in your initial test? The number format must be performed before assigning the value or it will not work because the leading zeros will have already been removed and while it will become text, it cannot replace the leading zeros. Anyway pleased for you that you have it working now so good luck. -- Regards, OssieMac "needhelp" wrote: > thank you OssieMac > > I had actually tried using > > ActiveCell.Offset(j, 0).NumberFormat = "@" > > in my code before posting my question and it didn't work. > > It does now, so i'm off to have a coffee! > > Cheers > Julie >
  • 3. Hide Ribbon In XLS Workbook When Using Excel 2007
    I am familiar with the ribbon, custom ribbon, callbacks, etc. from working with the ribbon in Access 2007, but am not that familiar with Excel. When opening an xls (Excel 2003) workbook using Excel 2007, is there a way to either hide the ribbon completely or load my own custom ribbon? If so, how? Also how can I disable some of the Office Button functions, like I can in Access? Thanks for any assisstance. -- AG Email: npATadhdataDOTcom
  • 4. Problem with retaining numbers formatted as text after code runs
    I am running code to check through each column in a worksheet and make substitutions for various characters, e.g. For i = 1 To numcols For j = 1 To numrows - 1 fvalue = ActiveCell.Offset(j, 0) Call cleanse_data ActiveCell.Offset(j, 0).Value = StrConv(fvalue, vbUpperCase) Next Sub cleanse_data() 'data cleansing routine fvalue = Application.WorksheetFunction.Substitute (fvalue, "&", " AND ") fvalue = Application.WorksheetFunction.Substitute (fvalue, "#", " NO. ") fvalue = Application.WorksheetFunction.Substitute (fvalue, "/", " ") fvalue = Application.WorksheetFunction.Substitute (fvalue, "/", " ") fvalue = Application.WorksheetFunction.Substitute (fvalue, "_", " ") fvalue = Application.WorksheetFunction.Substitute (fvalue, " ", " ") fvalue = Application.WorksheetFunction.Substitute (fvalue, " ", " ") End sub my problem is that when the value is written back into the cell "numbers" which were previously formatted as text revert to a number format, e.g. "000888" reverts to "888". How can I run this code, or similar code and retain the original formatting? I can't use a leading apostraphe as these values are uploaded to database system. Would appreciate any input ... Julie

Designing web pages for Excel Web Queries

Postby Chris Miller » Fri, 10 Oct 2003 03:07:18 GMT 

I'm running a website with ColdFusion MX and I've got 
account numbers on reports with leading zeros. When I 
export spreadsheets from Coldfusion the leading zeros are 
stripped out. Is there any html formating that would tell 
Excel to keep the leading zeros?
Top

Re: Designing web pages for Excel Web Queries

Postby Jake Marx » Fri, 10 Oct 2003 03:18:19 GMT 

Hi Chris,

You can learn a lot from saving an Excel workbook or sheet as an HTML
document.  Excel puts all sorts of XML attributes in the HTML tags that tell
Excel what to do when opening them.

This HTML source will produce a numeric value in cell A1 = 1 and a string in
cell A2 = 0001.


<html xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns=" http://www.**--****.com/ ">

<head>
 <title>Test</title>
</head>

<body>

<table>
 <tr>
  <td height=17 width=64 x:num>001</td>
 </tr>
 <tr>
  <td x:str="'00001">00001</td>
 </tr>
</table>

</body
</html>


So it looks like you can use x:str to specify that Excel should treat the
cell as a string.  If you give that attribute a value, I think Excel will
use that value instead of what's displayed in the HTML output.  In this
case, I put a single quote in front of the string so Excel won't reevaluate
the string as a number when you edit it.

-- 
Regards,

Jake Marx
www.longhead.com
Top

Re: Designing web pages for Excel Web Queries

Postby Jake Marx » Fri, 10 Oct 2003 04:13:22 GMT 

Chris,

Do the XML schema-related statements appear in the HTML tag?  Because the
HTML code below opens perfectly for me in Excel (Win XP, Excel 2002).

-- 
Regards,

Jake Marx
www.longhead.com
Top

Re: Designing web pages for Excel Web Queries

Postby Chris Miller » Fri, 10 Oct 2003 04:55:58 GMT 

Jake,
Even with the schema statements it doesn't see the cell as 
text. Excel 2000. That extra 2 makes all the difference 
apparently. I might just do this as an Excel macro 
instead. Thanks for your help.


tag?  Because the
Excel 2002).

it
sheet as an HTML
the HTML tags
A1 = 1 and a
Excel should
attribute a value, I
displayed in the
front of the
number when you

are
tell
Top

Similar Threads:

1.Web Queries and how Excel sees the "selected box" on the web page

2.Web query application - downloading ".shtml" web page to excel workbook

3.GIF image on web-page corrupts data fetched with Web Query 

I have an Excel workbook that is designed to fetch data from a web
page via a Web Query, that is invoked from a macro. The web pages
concerned have small GIF images about the size of the text font
floating near some of the text (as a substitute for an asterisk, it
seems). These symbols appear to corrupt the data which the Web Query
fetches- a variable amount of following data is lost.

How can I avoid this? It seems, for instance, that there might be a VB
command which will cause these GIF images to be ignored. Typical macro
script that invokes the query is:

Sheets("ALBE").Select
    Range("A26").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "FINDER;C:\QLDVTR\Queries\TESTIQY\TESTALBE.IQY", _
        Destination:=Range("A26"))
        .Name = "ALBE"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = False
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With 

Where the IQY file contains the web address

Is there something in the truth table which can be altered, or is
there something I can add to achieve what I want.

This problem occurs with Excel2003 and Excel2002

Thanks
Geoff Lambert

4.Web Query - additional web pages 

Hi, 
Does anyone know how to import data from a website that has numerous page 
containing relevant data?

e.g. searching for a particular item, the website show 10 pages that have 
the relevant item, I want to import dat from each of those 10 pages?

Thanks

5.web query 2003 returns no data from some web pages 

Some web pages return no data  for example the top ten stocks for this month 
from MSN stocks lists
Can anyone say why?  Other queries from other pages work fine?
Appreciate any help
Dianne Zinky

6. Web Query not downloading data filtered in web page

7. Saving Excel 2010 files as web page or single file web page

8. Web query: Want to pause macro while Web query completes refreshin

Post a reply

Return to MS EXCEL

 

Who is online

Users browsing this forum: No registered users and 61 guest

Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group