VBA outlook- Array length reduces when trying to transpose to 1D array.

Ebraham

Board Regular
Joined
Mar 21, 2015
Messages
215
Hi

I am trying to extract the data from csv to array using outlook vba. Below code works fine but when i try convert the array to 1D the lenght reduces from 250,000 to approx 58k. I saw some solution using Application.transpose to convert 2D in to 1D but i am getting the error while using application.transpose in outlook.

VBA Code:
Public email_array()

Sub load_emails_from_file()

strPath = "H:\XXXXX\XXXXX\"

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset

'Set cn = CreateObject("ADODB.Connection")

strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
Conn.Open strcon
strSQL = "SELECT CUSTOMER_NAME FROM Emaillist.csv;"

Dim rs As Recordset
Dim rsARR() As Variant

Set rs = Conn.Execute(strSQL)

email_array = WorksheetFunction.Transpose(WorksheetFunction.Transpose(rs.GetRows))
rs.Close
Set Conn = Nothing


End Sub
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,988
Office Version
  1. 2016
Platform
  1. Windows
why not use Powerquery to import the data - you get a lot more control and a lot less hassle.

From the Data Ribbon, Get & Transform Tab, New Query, From File, From CSV. This will open your CSV file in the Powerquery Editor where you have some pretty intuitive control over any changes. When you're happy with it, go to the Home Ribbon and select 'Close & Load', or 'Close and Load to' to open a dialogue with more control over where the data arrives in Excel.

Its worth the effort.

HTH
 

Ebraham

Board Regular
Joined
Mar 21, 2015
Messages
215
I don't want to use excel. I am performing some validations on emails in outlook. I am trying to load list of emails from csv and then I have events in outlook which triggers the event when the user clicks on reply, replyall ... etc.
So the issue is when I try to load the csv which has list of emails it's in 2D Array how can I convert the array to 1D using outlook vba. I saw some solutions using Application.transpose but that seems to not work in outlook vba, not sure y.
Any help is highly appreciated.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,951
When in doubt, write your own brute force routine
VBA Code:
For i = 1 to limit1
    For j = 1 to limit2
        newArray(i,j)= oldArray(j,i)
Next:Next
 

Ebraham

Board Regular
Joined
Mar 21, 2015
Messages
215

ADVERTISEMENT

When in doubt, write your own brute force routine
VBA Code:
For i = 1 to limit1
    For j = 1 to limit2
        newArray(i,j)= oldArray(j,i)
Next:Next
Loping takes a very long time I have approx 500k records, any other alternatives other than looping?
Error is similar to posted here Type mismatch

Thanks in advance. !!.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,951
With 500K records, you might look at not transposing. If its that large, you can work with it as a 2-D array. From the coding, it looks like a single row array.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
I don't want to use excel.
In that case, I'm wondering why ask in the Excel Questions forum?
Would I be best to move the thread to the General Discussion & Other Applications forum which is "for questions about applications other than Excel or Access"?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,093
Messages
5,622,664
Members
415,917
Latest member
kungsleden

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top