Steve
I gave up using the built in methods because of the issues I listed in my previous post. Also because I was always working with thousands of rows and speed is an issue. However with your application I think you have a fairly fixed format except where someone has edited the file where anything can happen.
Actually I don't use the line by line approach, instead I read the whole file all at once like this :-
Open sFullFileName For Binary As 66
sFileImage = String$(LOF(66), 32)
Get #66, 1, sFileImage
Close 66
I then use the Split function with the appropriate end-of-record marker to get an array which I then manipulate in memory. This is far faster than doing it in cells and far more flexible.
Freefile gets the next available file number to use in expressions like Open sFullFileName For Binary As 66 - where 66 is what you would get if 65 other files were already open. All over the web you will find people telling you that this is the proper way to do it but personally I think that this that you give up control by doing this and prefer to use hard coded magic numbers. This stops me working on the wrong file by mistake. In any case it is unusual for me to have more than two files open at once and do not need to go searching for new file numbers.
Note that the keyword 'Close' is both a VBA statement and also a method in the Windows Filesystem object. Do not confuse them.
Close by itself will close all open files (files opened with the 'Open' statement). Close 66 does not error if #66 is already closed or never opened.
In your attempt to close your CSV file use StrReverse(Split(StrReverse(sFullFileName), SLASH)(0)) to remove the path information.
Yes I do have a Wouxun radio and am very pleased with it. I sorted out the file structure about a year ago but got stuck on the write to radio from Excel. Thanks to one of your previous posts I now know how to do this, but there are at least three programs already in the public domain for this radio and life is short. Possibly validation of CTSS tones depending on service (Ham/PMR446/FRS) would be a useful improvement ?
73 Regards
Derek +++
>________________________________
> From: noskosteve <noskosteve@yahoo.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Friday, 8 June 2012, 4:45
>Subject: [ExcelVBA] Re: Opening a CSV file and populating cells just like Excel does
>
>
>
>
>Arrrrg! I now have a related question, but thanks both of you.
>
>Derek's method is what I called "manual" parsing of the records and hoped to stay away from that, unless necessary, though I have successfully done it before - see below.
>
>I couldn't immediately understand Dave's technique. So, rather than poking around and trying things to see if I could figure it out, I first tried to open the CSV as another Workbook, copy to an extra sheet in mine then close it, then scan the rows (Instead of the file) allowing me to "validate and modify" just swell. (I also enjoyed Derek's possible error areas and will keep that list close by - it all rings true.)
>
>This worked fine by first hardcoding (Via Macro recording) the CSV Open/Close.
>
>HOWEVER...
>
>When I added the standard OPEN File dialog stuff for user capture of the FileName, now I can't close the CSV Workbook.
>
>Macro recording gave me this for the hard coded close and it, of course, worked:
>Windows("710ctcss.csv").Close
>SO…
>I changed it to:
>Windows(FileName).Close
>Where: FileName = Application.GetOpenFilename(" bla … bla) (The regular Open Dialog)
>
>However, that (Windows-Close) errors as an out of subscript range, most likely because the var FileName holds the full pathname, not just the file - shown in the recording version.
>
>Looking at my previous adventures at _File_ reading (not Opening a Workbook), similar to the Way Derek suggests, I see that I got a file number with:
>FileNum = FreeFile ' A system function. Don't recall where I saw this technique.
>Then:
>Close FileNum
>Worked just fine (I think) for an Open File, but not for this Open Workbook. I get NO error, but the CSV-turned-Workbook doesn't get closed and FileNum appears to always be 1, not the typical random Handle type of thing.
>
>The Object Browser shows the Function FreeFile as needing a "Range number"; I obviously got away without one (default ??)
>
>Soooo…..
>
>Using:
>FileName = Application.GetOpenFilename(" TM-D710.csv data (*.csv), *.csv, bla…bla…bla"): Close
>If FileName = "False" Then Exit Sub
>Workbooks.Open FileName:=FileName
>
>How to Close the Workbook ?
>
>Regards (73), Steve
>P.S. Derek, that's an interesting filename there in your example. You obviously have one of those new China radios and must be reverse engineering their file protocol. FYI: this code is for transferring Kenwood DM-D710 memories to a Kenwood TS-2000 via a "CAT workbook" I developed some years ago.
>SEE:
>Manage Radio Memories with an Excel Spreadsheet !
>Click to tune. Click to capture Radio freq.
>All Memories on one page. Cut, Copy, Paste, Print 'em...
>For:
>TM-D700 | TH-F6A & F7 | TS-2000 | Icom 706MkIIG
>All PMs |Real-time & memories | Sat mems too | Loads Mem Tones!
>NOT D710|Also Kenwood.fx Files|
>
>More Details, Features and Downloads at:
>http://k9dci.home.comcast.net/
>
>
>
>
>
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
1 komentar:
To open, read, scan, extract and even recover *.xls, *,xlt, *.xlsx, xlsm, *.xltm, *.xltx, *.xlam file formats you may use http://www.recoverytoolbox.com/excel.html
Posting Komentar