See also the post "Extracting Data" posted by 'George' for ways to grab the
contents of a file, after which you can decide what you want to do with it.
Here was my code to grab rainfall data; it looks like I was parsing the web
page rather than opening a file, but I figured I'd post the code anyway in
case you see anything you can use.
HTH,
Keith
Sub ImportWeb()
For r = 1 To 8
TempYear = Choose(r, "96", "97", "98", "99", "00", "01", "02", "03")
For i = 1 To 12
TempMonth = Choose(i, "jan", "feb", "mar", "apr", "may", "jun",
"jul", "aug", "sep", "oct", "nov", "dec")
TempName = TempMonth & TempYear
Workbooks.Open FileName:="http://www.erh.noaa.gov/er/iln/climo/cmh"
& TempName
Sheets("cmh" & TempName).Select
Sheets("cmh" & TempName).Copy
After:=Workbooks("Destination.xls").Sheets(1)
Windows("cmh" & TempName & "://www.erh.noaa.gov/er/iln/climo/cmh" &
TempName).Activate
ActiveWindow.Close
Next
Next
End Sub
Sub ImportWeb2()
TempName = "dec" & "03"
Workbooks.Open FileName:="http://www.erh.noaa.gov/er/iln/climo/cmh"
& TempName
Sheets("cmh" & TempName).Select
Sheets("cmh" & TempName).Copy
After:=Workbooks("Destination.xls").Sheets(1)
Windows("cmh" & TempName & "://www.erh.noaa.gov/er/iln/climo/cmh" &
TempName).Activate
ActiveWindow.Close
End Sub
Sub Transform()
Dim TempArray(1 To 31, 1 To 2)
For r = 1 To 8
TempYear = Choose(r, "96", "97", "98", "99", "00", "01", "02", "03")
For i = 1 To 12
TempMonth = Choose(i, "jan", "feb", "mar", "apr", "may", "jun",
"jul", "aug", "sep", "oct", "nov", "dec")
TempName = "cmh" & TempMonth & TempYear
'TempName = "cmhSheet1"
Sheets(TempName).Select
Sheets(TempName).Columns("A:A").Select
For MyRow = 1 To 30
CheckSum = Sheets(TempName).Range("A" & Trim(Str(MyRow))).Value
If Left(CheckSum, 2) = " 1" Then
StartRow = MyRow
Exit For
End If
Next
p = 1
For j = StartRow To (StartRow + 30)
PullSum = Sheets(TempName).Range("A" & Trim(Str(j))).Value
TempArray(p, 1) = Val(Left(PullSum, 2))
TmpString = Left(PullSum, 31)
TempArray(p, 2) = Val(Right(TmpString, 5))
p = p + 1
Next
Sheets(TempName).Cells.Select
Selection.Clear
For outrow = 1 To 31
For OutCol = 1 To 2
UseOutCol = Choose(OutCol, "A", "B")
Sheets(TempName).Range(UseOutCol & Trim(Str(outrow))).Value
= TempArray(outrow, OutCol)
Next
Next
Next
Next
End Sub
Sub OneSheet()
Dim TempArray(1 To 31, 1 To 3)
For r = 1 To 8
TempYear = Choose(r, "96", "97", "98", "99", "00", "01", "02", "03")
For i = 1 To 12
TempMonth = Choose(i, "jan", "feb", "mar", "apr", "may", "jun",
"jul", "aug", "sep", "oct", "nov", "dec")
TempName = "cmh" & TempMonth & TempYear
'TempName = "cmhSheet1"
Sheets(TempName).Select
For j = 1 To 31
TempArray(j, 1) = TempName
TempArray(j, 2) = Sheets(TempName).Range("A" & Trim(Str(j))).Value
TempArray(j, 3) = Sheets(TempName).Range("B" & Trim(Str(j))).Value
'MsgBox j & " " & TempArray(j, 1) & " " & TempArray(j, 2) & " " &
TempArray(j, 3)
Next
For outrow = 1 To 31
useoutrow = (((((i - 1) * 8) + r) - 1) * 32) + (outrow)
For OutCol = 1 To 3
UseOutCol = Choose(OutCol, "A", "B", "C")
Sheets("cmhSheet1").Select
Sheets("cmhSheet1").Range(UseOutCol &
Trim(Str(useoutrow))).Value = TempArray(outrow, OutCol)
Next
Next
Next
Next
End Sub
"ker_01" wrote:
> I'll look tonight to see if I can still find it; Two moves ago, I had an
> Excel file that pulled daily rainfall data off of some government weather
> site (although that might have been served up as HTML, not a text file- I
> don't recall now). I suspect I just did a fixed width parse of each line into
> different columns; if your lines aren't fixed width you could either search
> for keywords using the built-in Excel capabilities, or use RegEx.
>
> If I find it, I'll post the relevant code late tonight, or tomorrow.
>
> If you have direct access (permissions) to the server where these files are
> stored, then it shouldn't be any different than cycling through local files
> and parsing out the data you need.
>
> "liam.mccartney" wrote:
>
> > Thank you.
> >
> > the CSV part I knew, unfortunately I'm working with .epw so they all need to
> > go text to column.
> >
> > Thanks again. I'll try those out.
> >
> > "Bernard Liengme" wrote:
> >
> > > If you use File | Open and point to a TXT file, Excel will import it into a
> > > worksheet. You can then use Data | Text to Columns to split the text into
> > > columns
> > > If the file has the extension CSV, more exciting things happen - items
> > > separated by commas get put into separate cells.
> > > Apologies it you already knew this
> > > best wishes
> > > --
> > > Bernard V Liengme
> > > Microsoft Excel MVP
> > > http://people.stfx.ca/bliengme
> > > remove caps from email
> > >
> > >
> > > "liam.mccartney" <(E-Mail Removed)> wrote in message
> > > news:63A3D197-DC86-42DD-944D-(E-Mail Removed)...
> > > > I've never used macros before in Excel.
> > > >
> > > > I'm trying to copy text from a file into a spreadsheet and copy a certain
> > > > set of out puts into another worksheet. 2000 times. (This is to compile
> > > > pertinent data provided by weather stations worldwide.)
> > > >
> > > > So what I want the macro to do is to open up the text file, copy the text,
> > > > and put it into the spreadsheet.
> > > >
> > > > Is it possible to automate the entire process? Can the macro move on to
> > > > the
> > > > next file and repeat the process for the new data? If so, how?
> > > >
> > > > All of these files are stored on a DOE website. Could I have the macro
> > > > load
> > > > the data straight from that server or would I need to have all the files
> > > > local on my computer?
> > > >
> > > > Sorry to bombard with questions.
> > > >
> > > > Hope someone can help me.
> > > >
> > > > Thank you!
> > >
> > >
> > >
Similar Threads
