Best Practice - Transfer Data

[Neon520]

Purpose: Copy 2 column of data from .csv file & Paste Special (value only) to
an Excel Template.
Question: What is the best way of achieving this?

What I tried #1: record a Macro, starting from the Template file > Open the
..csv file in Excel > Copy Needed 2 columns of data > Paste Special value only
starting E11 > Close .csv file > Answer No to the data on clipboard questions

Go back to Template File. > Stop the Macro recording.

Problem with Macro #1: I checked the code after recording this Macro, the
code doesn't specify File Name (report.csv) Nor the location of the file
(Desktop)
__________________________

What I tried #2: record a Macro, starting from the Template file > Unprotect
file, enter password > Run Import Text File Wizard > Choose Comma as
Delimiter > Go over column by column to select "Do not import column (skip)"
to skip the rest of the columns except the two columns needed.

Problem with Macro #2: (1) The Wizard seem to adjust the row height to fit
the font size. (2) Don't know how to embed the unprotect password to the
code, so that user won't need to enter it. (3) Also Don't know how to embed
the code to Protect the Templet after running the Wizard.

Other Variable Factor Known: Amount of rows of data that two columns might
be different from time to time.

I'm sorry if this is too lengthy, just want to make sure everyone understand.

Thank you in advance.

Neon520

 

[gimme_this_gimme_that]

Hi Neon,

You can edit Macro1 and specify a file name.

As and aside, regarding best practices: I always recommend *NOT* to
use copy and paste.

It's a killer on performance - it could make Excel hang for some
users.

Also, programmatically, it's easier because you know exactly how many
rows of data you've iterated through and you know exactly where to
start if you need to insert more data from another CSV.

I recommend that you iterate through the CSV a line at a time, but if
performance is *really* important, insert the data into the worksheet
in bursts of several rows at once instead of a row at a time.

 

[Dave Peterson]

In WinTel land, I'd use this kind of code to allow the user to browse for the
file:

dim myFileName as variant
....
myfilename = application.getopenfilename("CSV files, *.csv")
if myfilename = false then
'user hit cancel
exit sub
end if

With ActiveSheet.QueryTables.Add _
(Connection:="TEXT;" & myfilename, Destination:=activesheet.Range("E11"))

...

=====
But I have no idea how/if this would work on a Mac.

You may want to post your question here:
news://msnews.microsoft.com/microsoft.public.mac.office.excel
if you don't get a good response.