Using VBA in Outlook to transpose a CSV file in Excel

Question

So hopefully that topic isn't too confusing, but basically using VBA within Outlook I am parsing some emails I receive and then appending the results into a CSV File.

I am appending the results using the:

    Open X As Append As 1
    Print #1 yada yada
    Close

I know what the VBA code within Excel would be to copy and transpose the range as it is pretty straightforward, but can this be done within my Outlook VBA Macro? My goal would be to have this done after the data is appended to the CSV file.

I know to include a reference to Excel Objects in my references, but I'm not sure exactly how to put together the Excel code that will open the CSV file I have just appended to, transpose it, then save it as another CSV file. Thanks for any help!

Does it need to go into a CSV file before being transposed to Excel? what about going staight to Excel — Nathan Fisher

Nathan Fisher Nathan Fisher · Accepted Answer · 2011-06-22T01:42:27

If you have references to the Excel objects in your references then the code should basically be the same in the Outlook VBA as it is in Excel VBA.

Major diferences will be that you need to ensure that there is an open instance of an Excel Application and a Workbook/WorkSheet that you want to write to. Here is a basic macro for opening excel to get you started.

Dim excelApplication As Excel.Application  
Dim workbook As Excel.Workbook  
Dim sheet as Excel.WorkSheet
set excelApplication = new Excel.Application
set workbook = excelApplication.Open(<YourFilename>)
set sheet = workbook.WorkSheets(1)

'Do your stuff

workbook.Save
workbook.Close
excelApplication.Quit
set workbook = nothing
set excelApplicaiton = nothing

Note: this is just off the top of my head. I haven't tested it to make sure that it complies but should be pretty close.

Using VBA in Outlook to transpose a CSV file in Excel

1 Answers