Copying Text from a file into Excel w/ Macro

[liam.mccartney]

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!

 

[KC]

Sound exciting
Tell me more offline please

 

[Bernard Liengme]

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

 

[liam.mccartney]

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.

 

[ker_01]

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.

 

[ker_01]

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

 

[liam.mccartney]

This looks promising. Thanks!

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