Best Practice - Transfer Data
 

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.