PDA

View Full Version : Input Line



tatebn
05-04-2009, 02:38 PM
I'm trying to read the contents of a csv with a macro. I'm using Input Line to get the line from the input file. For some reason it reads the whole file instead of just the first line. Any theories?

Thanks,
Brandon

Bob Phillips
05-04-2009, 04:29 PM
Just open the CSV file as if it were an Excel file, it will open fine.

tatebn
05-04-2009, 05:06 PM
I'm reading like 30 csv's in and concatenating them. I'm doing it line by line so I can throw away the header line after the first one is loaded. So I can't just open it.

Bob Phillips
05-04-2009, 05:08 PM
Then open them one by one in VBA in a loop, pull off the first line you want, and close them.

tatebn
05-04-2009, 05:10 PM
That's exactly what I'm doing. But when I read the first line to throw it away, it reads the whole file as one line.

Kenneth Hobs
05-04-2009, 08:45 PM
It is hard to help without seeing code.

It should be easy to strip the first line, or just read all but the first one. There are many ways to do it.

To read all but the first line, set a counter and check if it equals 1 then don't read that line. Then set counter to 2 or add one for each line. Then you will also know how many lines it has.

If small files, I read it all into a text string or an array.

Bob Phillips
05-05-2009, 12:02 AM
That's exactly what I'm doing. But when I read the first line to throw it away, it reads the whole file as one line.

But I am saying to bin the In put Line approach and read it as a normal workbook. Then it is just a process of counting the lines, and copy 2:numlines to your master file.

tatebn
05-05-2009, 05:38 AM
I'm working with a non-working macro I inherited. I have no experience in the microsoft world. How would I open as a workbook and save to a new workbook?

Bob Phillips
05-05-2009, 06:08 AM
Someting like this



Dim Master As Workbook
Dim wbInput As Workbook

Set Master = ActiveWorkbook

Set wbInput = Workbooks.Open(Filename:="C:\Documents and Settings\xld\Desktop\Test.csv")
Range(Range("A2"), Range("A2").End(xlDown)).EntireRow.Copy Master.Worksheets("Sheet1").Range("A1")
wbInput.Close savechanges:=False

tatebn
05-05-2009, 06:24 AM
I'm assuming
Range(Range("A2"), Range("A2").End(xlDown))

Gets all rows from the second on down. So I would replace "A2" with "A1" if I wanted all rows. Which would then be copied.

But Once I have them copied how do I paste them at the end of the master file.

Is that what Master.worksheets("sheet1").Range("A1") does after the copy statement?

If so will it append or overwrite.

On another note, would it be possible to overwrite a sheet in the workbook that's running the macro with all the new data.

I have a workbook called wb1.xls that has 4 sheets.

Could I open that workbook run the macro and overwrite all info in "sheet1" with the info I'm getting from all the csv files?

I'm assuming it would be the same basic premise as before and the workbook calling the macro would be "ActiveWorkbook"

Is this correct?

If so, I still need to know how to append.

Thanks for all your help.

tatebn
05-05-2009, 07:19 AM
Also, when copying the data is there a way to ignore blank cells.

Some of the files are slightly off and blank cells need to be removed and the following cells bumped left.

Can I do this with the macro?

mdmackillop
05-05-2009, 07:19 AM
To paste below existing data
Range(Range("A2"), Range("A2").End(xlDown)).EntireRow.Copy Master.Worksheets("Sheet1").cells(rows.count,1).end(xlup).offset(1)