Exporting csv attachment to xls

Ed February 3, 2009 at 06:42:04
Specs: Windows XP
Good morning,

I receive an email overnight with an attached .csv file. I would like to automate the process of opening the file, copying the data and appending it to an existing .xls file.

The data can fall into one of three categories, and so the next step would be to check a column for the value and separate the pasting into different sheets.

I know I can figure out this last part on my own but the actual automated importing has stumped me.

Thanks for your help,


See More: Exporting csv attachment to xls

Report •

February 3, 2009 at 07:10:50
Jon? Mike? Have I stumped you guys? :)

Just kidding... any help is greatly appreciated

Thanks, Ed

Report •

February 3, 2009 at 07:35:05
I'll be honest, I'm rubbish at VBA in outlook. The excel bit sounds pretty easy, you're just looping through the file and depending on the value in column A copying to a different sheet. You can do this with a select case statement.

The way I'd play it is to save the csv in a fixed location, then open up your excel sheet, refresh your csv query, loop through it and copy / paste.

Something like (Note this is completely untested!)

sub getthatquery()

vars1max = sheet1.range("A65536").end(xlup).row +1 'assumes column A is populated in sheet 1
vars2max = sheet2.range("A65536").end(xlup).row + 1
vars3max = sheet3.range("A65536").end(xlup).row + 1
vars1text = "Sheet 1 Identifier"
vars2text = "Sheet 2 Identifier"
vars3text = "Sheet 3 Identifier"
varcsvlastrow = sheet4.range("A65536").end(xlup).row

    for irow = 2 to varcsvlastrow
    varcsvtext = sheet4.cells(irow, 1).value
    select case varcsvtext
    case vars1text
        sheet4.range(irow & ":" & irow).copy sheet1.range(vars1max & ":" & vars1max)
        vars1max = vars1max + 1
    case vars2text
        sheet4.range(irow & ":" & irow).copy sheet2.range(vars2max & ":" & vars2max)
        vars2max = vars2max + 1
    case vars3text
        sheet4.range(irow & ":" & irow).copy sheet3.range(vars3max & ":" & vars3max)
        vars3max = vars3max + 1
    case else
        sheet4.range(irow & ":" & irow).interior.colorindex = 4 'colours any row it can't import.
    end select
    next irow

end sub

Give it a try, hopefully you can work out the rest!

Report •

February 3, 2009 at 09:54:09
Thanks, Jon.

That is a very good answer, I didn't consider some of the logic. Thank you very much.

Report •

Related Solutions

February 5, 2009 at 11:01:46
Just an FYI...

re: sheet1.range("A65536").end(xlup).row +1

Now that 2007 allows for more than 65536 rows, the new "standard" for finding the last row is:

Sheet1.Range("A" & Rows.Count).End(xlup).Row

This method is backward compatible with previous versions, so it should adopted going forward.

Report •

Ask Question