Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Text from a file into Excel w/ Macro
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Text from a file into Excel w/ Macro
Sound exciting
Tell me more offline please "liam.mccartney" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Text from a file into Excel w/ Macro
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" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Text from a file into Excel w/ Macro
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" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Text from a file into Excel w/ Macro
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" wrote in message ... 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Text from a file into Excel w/ Macro
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" wrote in message ... 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Text from a file into Excel w/ Macro
This looks promising. Thanks!
"ker_01" wrote: 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" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro problem after copying an Excel File | Excel Discussion (Misc queries) | |||
Code for searching & copying Text from 1 text file to another | Excel Programming | |||
Copying certain lines from a text file | Excel Discussion (Misc queries) | |||
Macro copying info/data in multiple excel files into one summary file. | Excel Programming | |||
copying data from text file to excel sheet | Excel Programming |