Data importing prob

  • Thread starter Thread starter mohan
  • Start date Start date
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/21/2008 by jwarburg
'

'
With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.douanes.ci/Services/Grpproduit.asp?DateMaJ=29/02/2008", _
Destination:=Range("A1"))
.Name = "2008"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "5"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

End Sub
 
I modified the code to get the actual data. The access to the webpage is
slow. Below the code gets the first 3 chapters. Modify the code from 1 to 3
to 1 to 97 to get all the pages. The code creates a new worksheet for each
chapter. I you run the code more than once it will give you an error because
you are creating the same worksheet more than once.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 3/21/2008 by jwarburg
'

'
For chapt = 1 To 3
Worksheets.Add after:=Sheets(Sheets.Count)
ChaptNum = Format(chapt, "0#")
ChaptName = "Chapter " & ChaptNum
ActiveSheet.Name = ChaptName
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.douanes.ci/Services/resultpchap.asp?code=" & _
ChaptNum & "&DateMaJ=29/02/2008" _
, Destination:=Sheets(ChaptName).Range("A1"))
.Name = "2008"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "5"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next chapt
End Sub
 
Or you could create the webfetch and then use a loop to refresh the ONE
fetch and copy info to another page(s).
with sheets("getdat").querytables(1)
Then you only have one fetch and one external name and you only need the
..refresh background line


For i = 1 To 97
chaptnum = Format(i, "0#")
With Sheets("getdata").QueryTable(1)
.Connection = _
"URL;http://www.douanes.ci/Services/resultpchap.asp?code=" &
chaptnum & "&DateMaJ=29/02/2008"
.Refresh BackgroundQuery:=False
End With

'copy data somewhere

Next i
End Sub
 
Back
Top