Ethen5155
02-05-2017, 04:19 AM
Hi all,
well, i just need some help or any hint to add one more function to below code
Sub ExtractData()
Set Destn = Range("A1")
For i = 100 To 600
With ActiveSheet.QueryTables.Add(Connection:="URL;http://basm.kacst.edu.sa/ViewTerm.aspx?termid=" & i, Destination:=Destn)
.Name = "Test"
.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
Set Destn = Destn.Offset(.ResultRange.Rows.Count)
End With
Next i
Workbooks("Extracted.xlsm").Close SaveChanges:=True
End Sub
i need it to get data from web with count from 100 to 600 for ex. then export it to xlsx file at a specific path folder, then add (500) to last number and continue work again with same work flow
for ex.
Get data from 100 to 600 --> Save --> Export to 1.xlsx to (C:/Test/Extracted)
+500 to last number
Get data from 600 to 1100 --> Save --> Export to 2.xlsx to (C:/Test/Extracted)
+500 to last number
Get data from 1100 to 1600--> Save --> Export to 3.xlsx to (C:/Test/Extracted)
i need this modification because i have to separate data on many files to ignore the big size for each file that can prevent opening them.
i hope it can be done
Thanks a lot
Cheers
Cross-Posting: http://www.excelforum.com/showthread.php?t=1172681&p=4576034#post4576034
well, i just need some help or any hint to add one more function to below code
Sub ExtractData()
Set Destn = Range("A1")
For i = 100 To 600
With ActiveSheet.QueryTables.Add(Connection:="URL;http://basm.kacst.edu.sa/ViewTerm.aspx?termid=" & i, Destination:=Destn)
.Name = "Test"
.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
Set Destn = Destn.Offset(.ResultRange.Rows.Count)
End With
Next i
Workbooks("Extracted.xlsm").Close SaveChanges:=True
End Sub
i need it to get data from web with count from 100 to 600 for ex. then export it to xlsx file at a specific path folder, then add (500) to last number and continue work again with same work flow
for ex.
Get data from 100 to 600 --> Save --> Export to 1.xlsx to (C:/Test/Extracted)
+500 to last number
Get data from 600 to 1100 --> Save --> Export to 2.xlsx to (C:/Test/Extracted)
+500 to last number
Get data from 1100 to 1600--> Save --> Export to 3.xlsx to (C:/Test/Extracted)
i need this modification because i have to separate data on many files to ignore the big size for each file that can prevent opening them.
i hope it can be done
Thanks a lot
Cheers
Cross-Posting: http://www.excelforum.com/showthread.php?t=1172681&p=4576034#post4576034