Data importing prob

[mohan]

Hi,

I'm trying to get data imported from website to excel.

The link.
http://www.douanes.ci/Services/Grpproduit.asp?DateMaJ=29/02/2008

This is code 01 to 97 is called chapter wise data.
Click 01 data will come.
How can i extract hole data in one shot. (01 to 97)

Does anyone know a way
arround it? Anything would help.

Thanks!
Yours loving friend

 

[Joel]

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

 

[Joel]

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

 

[Don Guillett]

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