VBScript 将 HTML 导入 Excel 并创建图表

发布于 2024-11-02 18:08:30 字数 1010 浏览 0 评论 0原文

从下面的代码可以看出，我是 VBscripting for Excel 的新手。我只是尝试将 HTML 文件（“DEMO8.HTM”）导入 Excel 并根据 F 列中的数据创建图表。该图表应在创建的工作表中显示为对象。我在使用“Chart.Add”语法时遇到问题。每当我改变它时，就会出现其他问题。不管怎样，这是代码：

Dim chtChart

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\DEMO8.HTM")
objExcel.Visible = TRUE

Set chtChart = Charts.Add
With chtChart
    chtChart.ChartType = xlColumnClustered
    chtChart.SetSourceData.Range("'DEMO8'!$F:$F")
    chtChart.Location.xlLocationAsObject

    ActiveChart.SetSourceData Source:=Range("'DEMO8'!$F:$F")
    ActiveChart.Legend.Select
    Selection.Delete
    ActiveChart.ChartArea.Select
    ActiveChart.Axes(xlCategory).Select
    Selection.Delete
    ActiveChart.ChartArea.Select
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MaximumScale = 300
    ActiveChart.Axes(xlValue).MinimumScale = 0
End With

' Give the user control of Excel
UserControl = true

我基本上是通过各种代码来“弗兰肯斯坦”的。它似乎打开了 HTML 文件，但没有创建图表。任何帮助表示赞赏。

原文

As you can tell by the following code, I'm new to VBscripting for Excel. I'm simply trying to import an HTML file ("DEMO8.HTM") into Excel and create a chart based on the data in column F. The chart should show up as an object in the sheet created. I'm having a problem getting the 'Chart.Add' syntax to work. Whenever I change it, something else goes wrong. Anyway, here's the code:

Dim chtChart

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\DEMO8.HTM")
objExcel.Visible = TRUE

Set chtChart = Charts.Add
With chtChart
    chtChart.ChartType = xlColumnClustered
    chtChart.SetSourceData.Range("'DEMO8'!$F:$F")
    chtChart.Location.xlLocationAsObject

    ActiveChart.SetSourceData Source:=Range("'DEMO8'!$F:$F")
    ActiveChart.Legend.Select
    Selection.Delete
    ActiveChart.ChartArea.Select
    ActiveChart.Axes(xlCategory).Select
    Selection.Delete
    ActiveChart.ChartArea.Select
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MaximumScale = 300
    ActiveChart.Axes(xlValue).MinimumScale = 0
End With

' Give the user control of Excel
UserControl = true

I basically 'Frankensteined' this from various bits of code. It seems to open the HTML file but doesn't create the chart. Any help is appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

顾忌 2024-11-09 18:08:30

可能会让你更接近你想要的；

Dim chtChart, objWorkbook, objExcel

const xlValue = 2
const xlColumnClustered = 51
const xlCategory = 1

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\temp\DEMO8.html")
objExcel.Visible = True

Set chtChart = objWorkbook.Charts.Add

chtChart.ChartType = xlColumnClustered
chtChart.SetSourceData objExcel.Sheets("DEMO8").Range("F:F")

objWorkbook.ActiveChart.Legend.Select
objExcel.Selection.Delete
objWorkbook.ActiveChart.ChartArea.Select
objWorkbook.ActiveChart.Axes(xlCategory).Select
objExcel.Selection.Delete
objWorkbook.ActiveChart.ChartArea.Select
objWorkbook.ActiveChart.Axes(xlValue).Select
objWorkbook.ActiveChart.Axes(xlValue).MaximumScale = 300
objWorkbook.ActiveChart.Axes(xlValue).MinimumScale = 0

May get you closer to what you want;

Dim chtChart, objWorkbook, objExcel

const xlValue = 2
const xlColumnClustered = 51
const xlCategory = 1

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\temp\DEMO8.html")
objExcel.Visible = True

Set chtChart = objWorkbook.Charts.Add

chtChart.ChartType = xlColumnClustered
chtChart.SetSourceData objExcel.Sheets("DEMO8").Range("F:F")

objWorkbook.ActiveChart.Legend.Select
objExcel.Selection.Delete
objWorkbook.ActiveChart.ChartArea.Select
objWorkbook.ActiveChart.Axes(xlCategory).Select
objExcel.Selection.Delete
objWorkbook.ActiveChart.ChartArea.Select
objWorkbook.ActiveChart.Axes(xlValue).Select
objWorkbook.ActiveChart.Axes(xlValue).MaximumScale = 300
objWorkbook.ActiveChart.Axes(xlValue).MinimumScale = 0

回复收藏 0 原文

夜巴黎 2024-11-09 18:08:30

以下代码将 Excel 工作表转换为 html：

Dim oExcel
Dim objExcelWB
Dim objExcelWS, WScript
Dim strColumnCount, strTotRows, strData, strTable

Dim i, j, objFSO, objtxt
Dim strWbk
strWbk = "<excel sheet path>"
Dim strWsheetName
strWsheetName = "<workbook name>"
Dim strHTMLFile
strHTMLFile = "<html path>"
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False

Set objExcelWB = oExcel.Workbooks.Open(strWbk)

Set objExcelWS = objExcelWB.Worksheets(strWsheetName)
'Getting the rows and column count
 strColumnCount = objExcelWS.UsedRange.Columns.Count
 strTotRows = objExcelWS.UsedRange.Rows.Count

strTable = "<table border=""""1"""">"
'Create html table based on content of the excel file
 For j = 1 To strTotRows
  strTable = strTable & "<tr>"
  For i = 1 To strColumnCount
    strData = Trim(objExcelWS.Cells(j, i))
    strTable = strTable & "<td>" & strData & "</td>"
  Next
  strTable = strTable & "</tr>"
 Next
 strTable = strTable & "</table>"
 Set objFSO = CreateObject("scripting.FileSystemObject")
 Set objtxt = objFSO.createTextFile(strHTMLFile)
    objtxt.write (strTable)
'Closing the workbook
 objExcelWB.Close
oExcel.Quit
 Set objFSO = Nothing
Set objExcelWB = Nothing
Set oExcel = Nothing

The following code will convert excel sheet into html:

Dim oExcel
Dim objExcelWB
Dim objExcelWS, WScript
Dim strColumnCount, strTotRows, strData, strTable

Dim i, j, objFSO, objtxt
Dim strWbk
strWbk = "<excel sheet path>"
Dim strWsheetName
strWsheetName = "<workbook name>"
Dim strHTMLFile
strHTMLFile = "<html path>"
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False

Set objExcelWB = oExcel.Workbooks.Open(strWbk)

Set objExcelWS = objExcelWB.Worksheets(strWsheetName)
'Getting the rows and column count
 strColumnCount = objExcelWS.UsedRange.Columns.Count
 strTotRows = objExcelWS.UsedRange.Rows.Count

strTable = "<table border=""""1"""">"
'Create html table based on content of the excel file
 For j = 1 To strTotRows
  strTable = strTable & "<tr>"
  For i = 1 To strColumnCount
    strData = Trim(objExcelWS.Cells(j, i))
    strTable = strTable & "<td>" & strData & "</td>"
  Next
  strTable = strTable & "</tr>"
 Next
 strTable = strTable & "</table>"
 Set objFSO = CreateObject("scripting.FileSystemObject")
 Set objtxt = objFSO.createTextFile(strHTMLFile)
    objtxt.write (strTable)
'Closing the workbook
 objExcelWB.Close
oExcel.Quit
 Set objFSO = Nothing
Set objExcelWB = Nothing
Set oExcel = Nothing

回复收藏 0 原文

~没有更多了~