-
VBA to copy .txt file
Hi all,http://www.mrexcel.com/forum/images/...icon_smile.gif
Can any one help me out with my requirement.
i have a txt file which system generates automatically daily.
1) i will download the .txt file, copy the contents, paste it into the excelsheet then delimit the contents.
2)downloaded contents contains headers like INDEXING, REVIEWING and ERRORS. All i need to do is copy the contents under INDEXING and paste it to an already existing saved excel file in the sheet naed index...
similarly for reviewing and errors.. and the values will not be constant and also volumes.
i am not sure whether this can be done using macros. This, i was doing manually and if i get a coding for this it will be highly helpful.:help
Thanks in advance !!!!
-Sindhujahttp://www.mrexcel.com/forum/images/smilies/tongue.gif
-
Howdy. I changed the thread title to make it more specific to your needs.
-
Yes, it can all be done automatically.
I'd be happy to help you if you give it a shot first.
First see if you can grab the data using a web query. I've also never tried to read an text file directly from a web address using filesystemobject. Wonder if that can be done.
Once you have the data file in Excel, then do Record Macro and parse your data. Stop recording the Macro. Examine the coding. Can you understand it? Can you apply it to this problem in the future when there's a new text file?
Anyways, I'd start with the web query.
Good luck!
-
-
Hi All,
I have attached the sample file.
The bolded ones in Sheet1 is to be copied to the concern tabs...
-Sindhuja
-
Hi All,
Please help me out in this...
-Sindhuja
-
i tried something like this (not totally automatic) for your first 2 items. the 3rd item doesn't quite work (are you needing only 2 columns from this?)
copy your data only, without the headings into a Sheet2 and run this
[vba]
Option Explicit
Sub TrimXcessSpaces()
'Macro Purpose: To trim all excess spaces out of cells. This
'eliminates issues where users have cleared the cell with a space,
'and elimates all extra spaces at the beginning or end of a string
Dim cl As Variant
'Loop through cells removing excess spaces
For Each cl In Selection
If Len(cl) > Len(WorksheetFunction.Trim(cl)) Then
cl.Value = WorksheetFunction.Trim(cl)
End If
Next cl
End Sub
[/vba]
Perform a TextToColumns -->Delimited--> Delimiters :Other (type in a Space),
then copy & paste the resulting data into your designated sheet. there is coding that can be done but i'm not the best of candidates to write that.
repeat code as needed for your other data...
the coding Sub TrimXcessSpaces is courtesy of Ken Puls over here:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=616
i believe that coding can be done up front to extract the data from your text file
hth
