Add data to excel worksheets from text file

[Guest]

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

 

[excel-ant]

Something Like this;-

http://somethinglikeant.110mb.com/excel-ant/examples/Import_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

 

[Guest]

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

 

[Guest]

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

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

Something Like this;-

http://somethinglikeant.110mb.com/excel-ant/examples/Import_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