Using Arrays vs copy paste

mark hansen

Well-known Member
Joined
Mar 6, 2006
Messages
534
Office Version
  1. 2016
Platform
  1. Windows
I have a process working in a few areas where multiple people are tasked with reviewing information and entering their evaluation in a spreadsheet. Because more than one person may need to enter this information at the same time, I have them entering their information in a data entry workbook and send that information to a CSV file. The main workbook, that analyzes the compiled information, will read in the CSV file and put it in a table. (The evaluators pull the data entry file from SharePoint, so they can all use the same file at the same time.)

As I have yet be able to figure out how to (more gracefully) append the table with the CSV file, I came upon using this method:
-I have a background worksheet, on the main analytics file, that pulls in the CSV file via a data connection.
-Then I find how many rows came in this time, and copy that area to clipboard.
-Next I find the next available row under the table and paste (special) the information from the background worksheet.
-Then resize the table to include the additional information.
-Pop up a message box telling the user how many new rows were added to the table.

Typically I don't bring in more than 10-20 rows at a time, but at one time I brought in 2000 + rows at one time.

A friend told me rather than using the above steps, I should read the CSV file into an array and print the array to the information directly to the table. In testing, I did get this to work, but it was painfully slow. So slow, when I turned on screen updating, I could see each row go into the table. It wasn't much better when I turned off screen updating. I didn't find a method of directly going to the table. I presume as I wrote the lines of new data one by one, under the table, the table automatically resizing was taking the time. When I pasted the information from the background worksheet, the table didn't resize, until I resized it with VBA code.

I'm still learning how to use arrays, it's not the first method I use to work with data. I am now able to read in the CSV file and put it into a 2D array, but having problems with effectively getting that information into the table.

I guess the question is, with the normal amount of rows in my CSV files, is there any real benefit to using an arrays to capture the data, vs using the method above?
Is there a way to read a CSV file directly into a table I haven't found yet?
Or perhaps is there a more efficient to append a table from an array?

Thanks for any insight provided.

Mark
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,769
Office Version
  1. 2010
Platform
  1. Windows
Is there a way to read a CSV file directly into a table I haven't found yet?
Via the VBA QueryTable for example, samples in every Excel forums …​
Or copy all in a single column then with the Excel feature Text To Column even under VBA …​
Or …​
 

mark hansen

Well-known Member
Joined
Mar 6, 2006
Messages
534
Office Version
  1. 2016
Platform
  1. Windows
Thanks marc. I haven't gotten into power Query because we don't have that available in our company.
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,769
Office Version
  1. 2010
Platform
  1. Windows
Not sure if I well catch you but the old QueryTable (exists maybe since last century !) has nothing to do with the new Power Query …​
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,648
Office Version
  1. 365
Platform
  1. Windows
I am now able to read in the CSV file and put it into a 2D array, but having problems with effectively getting that information into the table.
Actually it's pretty straightforward to return 2D array values to a range.

Here's an example:

VBA Code:
Dim va
va = Range("A1:C10")
Range("E1").Resize(UBound(va, 1), UBound(va, 2)) = va
the code loads Range("A1:C10") values to va (2D array) then return it to a range (start at E1).

in your case, return the array to the row below the table, then you can resize the table by using CurrentRegion, providing the table is separated from other data on the sheet. Like this:

VBA Code:
With Sheets("Sheet1").ListObjects("Table1")
.Resize .Range.CurrentRegion
End With

And just another idea, but untested:
1. Open the csv file directly in Excel.
2. Copy the content.
3. Go to the table, add 1 blank new row at the bottom.
4. Paste the content to the new row, the table will resize accordingly.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,038
Messages
5,767,784
Members
425,434
Latest member
ecrodrig

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