Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add data to excel worksheets from text file
Gurus,
I am an excel user but new to programming excel. Here is my requirement and need your help. I have a text file that has the following format. City ReportedCrimes UnreportedCrimes Jackson 4 3 Florence 3 3 Newark 6 4 I get the report every week. I have an excel that has worksheets by citynames. ie. jackson, florence and newark. Each work sheet has the same format as above text file. (i.e columns) Here is what i want. (a)Every week when i get the report i want a macro which upon clicking take the text file as input , and add a row in the respective city worksheets. So the worksheets will have all the data each week in a row. How can i do this. Please give me sample code. Truely appreciate your help Regards, Anvoice |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add data to excel worksheets from text file
Something Like this;-
http://somethinglikeant.110mb.com/ex...Crime_Data.xls Code --------------------------------------------------------------------- Sub ImportCrimeStats() 'switch calculation off to speed up macro Application.Calculation = xlManual 'Pick up file path information qfolder = [B5]: qfile = [B8] 'set counter a , b, c, k = 0 'find where to write in the data sheets a = Sheets("Newark").[A1].End(xlDown).Row + 1 b = Sheets("Jackson").[A1].End(xlDown).Row + 1 c = Sheets("Floence").[A1].End(xlDown).Row + 1 'Open Text File Open qpath & qfile For Input Access Read As #1 Do Until EOF(1) Line Input #1, qdata 'variables If UCase(Left(qdata, 4)) < "CITY" Then q1 = Trim(Mid(qdata, x1, y1)) q2 = Trim(Mid(qdata, x2, y2)) 'Go to city sheet and write data Sheets(q1).Select If q1 = "Newark" Then k = a If q1 = "Jackson" Then k = b If q1 = "Florence" Then k = c Cells(k, 1) = Date Cells(k, 2) = q1 Cells(k, 3) = q2 End If Loop Close #1 Sheets("Macro").Select 'reset calculation Application.Calculation = xlCalculationAutomatic End Sub ------------------------------------------------------------------------- We need to know what the structure of the text file is to replace x1,x2,y1,y2 with numbers for the MID function to work. I have also made an assumption about the content of the "City" sheets. You will need to input the File Path and File Name of your weekly text file into the Macro tab. But you should be able to tweak this to your needs. Let me know if you need anything further. http://www.excel-ant.co.uk |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add data to excel worksheets from text file
Dear Sir,
Thank you so much. It did work. I have noticed the following (a)a = Sheets("Newark").[A1].End(xlDown).Row + 1 was returning 65537 when the file is blank. When it has atleast one row then it is working well. (b)If i do not want to put the file path and file name in B5 and B8 cells in macro worksheet, how can i reference it directly? i.e Can i specify Open 'C:\Adi\Adi\sample.txt' For Input Access Read As #1 ? (c)While processing if there is an error ie invalid data how can i trap it and let it continue with next row instead of abending, reporting the errors at the end? (this has not happened yet) Regards, Anvoice "excel-ant" wrote: Something Like this;- http://somethinglikeant.110mb.com/ex...Crime_Data.xls Code --------------------------------------------------------------------- Sub ImportCrimeStats() 'switch calculation off to speed up macro Application.Calculation = xlManual 'Pick up file path information qfolder = [B5]: qfile = [B8] 'set counter a , b, c, k = 0 'find where to write in the data sheets a = Sheets("Newark").[A1].End(xlDown).Row + 1 b = Sheets("Jackson").[A1].End(xlDown).Row + 1 c = Sheets("Floence").[A1].End(xlDown).Row + 1 'Open Text File Open qpath & qfile For Input Access Read As #1 Do Until EOF(1) Line Input #1, qdata 'variables If UCase(Left(qdata, 4)) < "CITY" Then q1 = Trim(Mid(qdata, x1, y1)) q2 = Trim(Mid(qdata, x2, y2)) 'Go to city sheet and write data Sheets(q1).Select If q1 = "Newark" Then k = a If q1 = "Jackson" Then k = b If q1 = "Florence" Then k = c Cells(k, 1) = Date Cells(k, 2) = q1 Cells(k, 3) = q2 End If Loop Close #1 Sheets("Macro").Select 'reset calculation Application.Calculation = xlCalculationAutomatic End Sub ------------------------------------------------------------------------- We need to know what the structure of the text file is to replace x1,x2,y1,y2 with numbers for the MID function to work. I have also made an assumption about the content of the "City" sheets. You will need to input the File Path and File Name of your weekly text file into the Macro tab. But you should be able to tweak this to your needs. Let me know if you need anything further. http://www.excel-ant.co.uk |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add data to excel worksheets from text file
Dear Sir,
How can i do exactly the same funcationality if the input in an excel file(.xls) Because i am having problems with text files, with data not being in same position always resulting in garbage. Regards, Anvoice "Anvoice" wrote: Dear Sir, Thank you so much. It did work. I have noticed the following (a)a = Sheets("Newark").[A1].End(xlDown).Row + 1 was returning 65537 when the file is blank. When it has atleast one row then it is working well. (b)If i do not want to put the file path and file name in B5 and B8 cells in macro worksheet, how can i reference it directly? i.e Can i specify Open 'C:\Adi\Adi\sample.txt' For Input Access Read As #1 ? (c)While processing if there is an error ie invalid data how can i trap it and let it continue with next row instead of abending, reporting the errors at the end? (this has not happened yet) Regards, Anvoice "excel-ant" wrote: Something Like this;- http://somethinglikeant.110mb.com/ex...Crime_Data.xls Code --------------------------------------------------------------------- Sub ImportCrimeStats() 'switch calculation off to speed up macro Application.Calculation = xlManual 'Pick up file path information qfolder = [B5]: qfile = [B8] 'set counter a , b, c, k = 0 'find where to write in the data sheets a = Sheets("Newark").[A1].End(xlDown).Row + 1 b = Sheets("Jackson").[A1].End(xlDown).Row + 1 c = Sheets("Floence").[A1].End(xlDown).Row + 1 'Open Text File Open qpath & qfile For Input Access Read As #1 Do Until EOF(1) Line Input #1, qdata 'variables If UCase(Left(qdata, 4)) < "CITY" Then q1 = Trim(Mid(qdata, x1, y1)) q2 = Trim(Mid(qdata, x2, y2)) 'Go to city sheet and write data Sheets(q1).Select If q1 = "Newark" Then k = a If q1 = "Jackson" Then k = b If q1 = "Florence" Then k = c Cells(k, 1) = Date Cells(k, 2) = q1 Cells(k, 3) = q2 End If Loop Close #1 Sheets("Macro").Select 'reset calculation Application.Calculation = xlCalculationAutomatic End Sub ------------------------------------------------------------------------- We need to know what the structure of the text file is to replace x1,x2,y1,y2 with numbers for the MID function to work. I have also made an assumption about the content of the "City" sheets. You will need to input the File Path and File Name of your weekly text file into the Macro tab. But you should be able to tweak this to your needs. Let me know if you need anything further. http://www.excel-ant.co.uk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter data from two worksheets from same excel file | New Users to Excel | |||
linking data from text file on ftp server to excel file | Excel Programming | |||
copy Excel worksheets into one text file | Excel Programming | |||
importing text file, removing data and outputting new text file | Excel Programming | |||
Open delimited text file to excel without changing data in that file | Excel Programming |