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 he
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:Neon:Desktop: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

" 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