我有一个电子表格，其中包含A列中的URL列表。我想读取每个URL并将输出存储在单个单元格中，或者至少存储在与URL相同的行中。 我目前正在使用以下标准VBA代码来执行每次读取（此处简化为使用静态URL）。
问题是Excel会自动将数据分成多行和多列，具体取决于网页的格式（这显然是通常需要的）。
是否有任何简单的方法来修改该代码以强制输出到单个单元格或行？
Sub FetchData() With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.someurl", Destination:=Range("$B$1")) .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End Sub别担心，我设法使用此处的代码来解决这个问题： 仅将特定表从网站提取到Excel中
我的下面的版本假设列A包含URL列表，列BI包含其他数据。 我想从A列读取每个URL的网页，从表中提取2个字段并将它们放在J和K列（第10和11列）中。 这些字段在网页中命名为ID =“Name1”和ID =“Name2”。
Sub Getfromweb() Dim RowNumb As Long Dim LastRow As Long Dim htm As Object Set htm = CreateObject("htmlFile") LastRow = Cells(Rows.Count, 1).End(xlUp).Row For RowNumb = 1 To LastRow With CreateObject("msxml2.xmlhttp") .Open "GET", Sheets(1).Cells(RowNumb, 1), False .send htm.body.innerhtml = .responsetext End With With htm.getelementbyid("Name1") Sheets(1).Cells(RowNumb, 10).Value = .innertext End With With htm.getelementbyid("Name2") Sheets(1).Cells(RowNumb, 11).Value = .innertext End With Next RowNumb End SubI have a spreadsheet with a list of URLs in column A. I want to read each URL and store the output in a single cell, or at least into the same row as the URL. I am currently using the following standard VBA code to do each read (simplified here to use a static URL).
The problem is Excel is automatically separating the data into multiple rows and multiple columns, depending on the format of the web page (which is obviously what would usually be wanted).
Is there any simple way of modifying that code to force the output into a single cell or row please?
Sub FetchData() With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.someurl", Destination:=Range("$B$1")) .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End SubDon't worry, I managed to figure it out using code from here: Fetch specific table only from website into Excel
My version below assumes column A contains a list of URLs, and columns B-I contain other data. I want to read the web page for each URL from column A and pull out 2 fields from a table and put them in columns J and K (columns 10 and 11). The fields are named ID="Name1" and ID="Name2" in the web pages.
Sub Getfromweb() Dim RowNumb As Long Dim LastRow As Long Dim htm As Object Set htm = CreateObject("htmlFile") LastRow = Cells(Rows.Count, 1).End(xlUp).Row For RowNumb = 1 To LastRow With CreateObject("msxml2.xmlhttp") .Open "GET", Sheets(1).Cells(RowNumb, 1), False .send htm.body.innerhtml = .responsetext End With With htm.getelementbyid("Name1") Sheets(1).Cells(RowNumb, 10).Value = .innertext End With With htm.getelementbyid("Name2") Sheets(1).Cells(RowNumb, 11).Value = .innertext End With Next RowNumb End Sub最满意答案
显然它与文本到列有关。 如果之前已使用过，并且将空白指定为分隔符，则它将应用于将来的数据导入。 奇怪的。 无论如何只需进入Text to Columns并删除空白分隔符并接受，然后重做数据导入。
Apparently it relates to Text to Columns. If that has been used previously and a blank was specified as the separator then it gets applied to future data imports. Weird. Anyway just go into Text to Columns and remove the blank separator and accept, then redo the data import.
更多推荐
发布评论