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

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.

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/microsof...c.office.excel
if you don't get a good response.

Neon520 wrote:
>
> Hi there,
> Thank you for your response to my questions.
>
> Macro #2 seems to work pretty well for me, so I'm going to stick with it.
> But there are a few glitches that I would like to fix before it can put to
> use.
>
> Here is the code:
> Sub Macro2()
> '
> ' Macro2 Macro
> ' Macro recorded 2/17/2009 by Neon
> '
> ' Keyboard Shortcut: Option+Cmd+z
> '
> ActiveSheet.Unprotect
> ActiveSheet.Unprotect Password:="test"
> Range("E11:F113").ClearContents
>
> With ActiveSheet.QueryTables.Add(Connection:= _
> "TEXT;Mac HD:Users:Neonesktop:report.csv",
> Destination:=Range("E11"))
> .Name = "report_6"
> .FieldNames = True
> .RowNumbers = False
> .FillAdjacentFormulas = False
> .RefreshOnFileOpen = False
> .BackgroundQuery = True
> .RefreshStyle = xlInsertDeleteCells
> .SavePassword = False
> .SaveData = True
> .AdjustColumnWidth = True
> .TextFilePromptOnRefresh = False
> .TextFilePlatform = xlMacintosh
> .TextFileStartRow = 1
> .TextFileParseType = xlDelimited
> .TextFileTextQualifier = xlTextQualifierDoubleQuote
> .TextFileConsecutiveDelimiter = False
> .TextFileTabDelimiter = True
> .TextFileSemicolonDelimiter = False
> .TextFileCommaDelimiter = True
> .TextFileSpaceDelimiter = False
> .TextFileColumnDataTypes = Array(9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9,
> 9, 9, 9, 9, 1, 1, 9, 9, 9, _
> 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9)
> .Refresh BackgroundQuery:=False
> .UseListObject = False
> End With
>
> ActiveSheet.Protect
> ActiveSheet.Protect Password:="test"
> Range("D11").Select
> End Sub
>
> 1. The code doesn't seem to register the Protect Password that I would like
> it protect the sheet with. When I unprotect the sheet, there is NO password.
> 2. The Import Text File Wizard RESIZE the row height to FIT font size, how
> can I avoid this?
> 3. Can anyone please help me modify the above codes, so that there is an
> error message and an OK button pop up when report.csv file is not on the
> desktop? (Error Handling purpose)
>
> Thank you ,
> Neon
>
> "(E-Mail Removed)" wrote:
>
> > 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