Large txt file import macro

All,
 I asked a longtime ago for a macro to allow large text 
files to be imported over several sheets within one work 
book, this worked great, however my machine has been 
rebuilt and I failed to keep the macro, 

can some nice person please supply a link again .

Many thx
Larry.
0
anonymous (74722)
9/15/2004 10:37:55 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
278 Views

Similar Articles

[PageSpeed] 29

Larry,

The usual code is below.

HTH,
Bernie
MS Excel MVP

Sub LargeFileImport()
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = Application.GetOpenFilename
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add Template:=xlWorksheet
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If
'For xl95 change 65536 to 16384
If ActiveCell.Row = 65536 Then
'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False
End Sub

"Larryl" <anonymous@discussions.microsoft.com> wrote in message
news:28a801c49b10$0fc6b800$a301280a@phx.gbl...
> All,
>  I asked a longtime ago for a macro to allow large text
> files to be imported over several sheets within one work
> book, this worked great, however my machine has been
> rebuilt and I failed to keep the macro,
>
> can some nice person please supply a link again .
>
> Many thx
> Larry.


0
Bernie
9/15/2004 12:38:33 PM
Reply:

Similar Artilces:

Pictures with Macro's attached to it are displayed as red cross
Hello, I am having trouble with spreadsheets with macro's in it: in the excel sheet there are icons/pictures which have a macro attached to it. When you click the icon/picture the macro will execute. When the user, running Windows XP SP1 and Excel 2002 with macro security on medium, opens the excel sheet the icons/pictures are displayed as a red cross, just as if the picture is missing. When I open the sheet the pictures are displayed correctly. I am runnig Windows XP SP2 and Excel 2003. My macro security is set to high. Any suggestions to a solutions. I have not found anything y...

File extensions
I have a macro that when running activates a file that is already open, this file is called 'File1'. When 'File1' is active it is shown as 'File1.xls' but the macro is looking for 'File1' (no extension) and cannot find it. This is a problem that I have only had recently and all other macro's/links have worked. I have not upgraded to a newer version of Excel recently (currently using Office '97 I think). Is there a way of rectifying this without having to amend every macro/link that I am using? I don't think this is an Excel problem, unle...

Is there a way to unload the loaded XLL file in Excel? Hi all, I am debugging XLL link library using Visual C++. Everytime I rebuild the XLL, I have to close the whole Excel program and relaunch t
Is there a way to unload the loaded XLL file in Excel? Hi all, I am debugging XLL link library using Visual C++. Everytime I rebuild the XLL, I have to close the whole Excel program and relaunch the Excel program again, and then load in the newly generated XLL library again... This is tedious... I am looking for a way to unload XLL (already loaded) from within EXCEL program... Any thoughts? (I find just close the currently active worksheet doesn't get the XLL unloaded...) ...

Saving OFX files from California Bank Of America...convert ofx?
All, I've seen similar posts, but not exactly the same. I saved an OFX from California BofA, and I can no longer retrieve the data in a different format (it's older than their three month deadline). When I try to import it, I get the familiar 'There is a problem with the data received from this online provider. If the problem persists, call the provider's customer service number for help.' Can I convert the OFX to OFC or QIF (which had worked for me previously). It seems like there are conversion utilities for almost every other format to OFX, but not from ...

Macro to produce Gantt Chart
I am trying to produce a Gantt Chart based on a set of Start Dates and End Dates for Activities. I do not want the Gantt to be coloring cells but drawing a rectangle with a certain color. A manually produced sample Gantt Chart is attached to clarify my idea. I would be interested in a macro that would read the cells containing the time scale (Jan to Jul 2004 in cells E5 to K5), read the start and end dates (cells C6 to D9) of the activities and produce the Gantt Bars in a color to be selected by the user. If this would make it easier, reading the start and end dates from the current row an...

What is File = 1
Hi, In my SQL Server 2008, I tried to restore some db. I searfed the internet and found some command like Restore MyDB FROM DISK = 'C:\BackUp\.bak' WITH file=1, RECOVERY I am wondering what the 'file = 1 is. Thanks for help. Jason In this context it is the first backup within the backup file. Unless you use INIT or FORMAT within the BACKUP command any new backups you do will add to the current file (if one exists). To choose between the backups within a file you use the FILE = integer clause. Fitz "Jason Huang" <JasonHuang8888@hotma...

Opening an excel file using a script
Hi, I have created a spreadsheet containing some macros. I need to write a script of some sort that could run the spreadsheet (allowing the macros to run) on a scheduled basis. does anyone know how I can do this? also, is there a way of the system opening an excel spreadsheet without actually visibly doing so (no MS Excel being open etc)? the macros int he sheet refresh data from an external source, and email a specified user. thanks in advance, Matt -- matpj ------------------------------------------------------------------------ matpj's Profile: http://www.excelforum.com/member.p...

import address book from Corel WP 8 to Outlook 2000 Contacts
I have tried unsuccessfully to import data from the Corel WordPerfect 8 address book format into Outlook 2000 contacts The export format from Corel WordPerfect is designated .abx, although it is essentially just a comma separated file One problem seems to be that the import options available under the menu in Outlook do not support Corel, etc Does anyone know how one might take the data from the Corel address book format and convert it to a .csv format that Outlook will then accept? I need to be able to import addresses and phone numbers, not just names and e-mail addresses I have tried c...

Shorcuts or Macro for Creating If Else Statements
I create if else statements often. But I find it tedious to have to type them and redefine them all the time for different workbooks. For example, let's say you want this type of if else condition for calculating price to earnings: a1=price; b1=earnings =if ( iserror( a1/b1),"n/a", if (or( a1/b1<0, a1/b1>100),"nmf", a1/b1)) Is there a way of shortening this? What I mean is, of course I can copy this down the column and calculate a bunch of P/Es for a bunch of companies. I'm trying to see if there is a macro or short cut that will help you create if el...

Macro to move to specified cells, pause for input at each?
-- Thanks Much! Pete Pete Is there a question here or are you just saying thanks to a response you received to an earlier post? If a question, please provide more detail or check out VBE help on "inputbox". Gord Dibben Excel MVP On Fri, 4 Mar 2005 17:41:01 -0800, "Pete in Montrose" <PeteinMontrose@discussions.microsoft.com> wrote: -- Thanks Much! Pete ...

Import Data into Excel from USNB Device
I need help :- I have purchased a couple of these devices: (USB Pen Drive for Reading RFID tags) http://www.rfidshop.com/index.asp?function=DISPLAYPRODUCT&productid=1120 XP Home 97 Excel I want to import info into Excel via the USB RFID reading device (via Human Interface Device). Using one device works fine and places info in Excel at the active cell. Using two devices both readings go in same column again in the active cell. However I want to be able to recognise indivdually 2 or more USB ports? and then tell Excel in which column each devices info is to go. How do I get to ...

how can I get old replaced excel file
I have one A file, suddenly I have go to sav as option & select file B to replace with A I said yes, but my mistake I said yes. Please see how can I get that old A file from back date ? Use the backup copy. If you didn't create a backup last time, make sure that you do next time. In your "Save As" dialogue, "General Options" comes from the Tools option (in Excel 2007). If you didn't create a backup copy yourself, then you are out of luck unless your system (or your IT department if you have one) has created a backup, or unless you sent a cop...

how to prompt user to click on cell for starting import position
This is a multi-part message in MIME format. ------=_NextPart_000_0018_01C3503B.424EF830 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable How would this be changed so that when the macro is run, it prompts the = user to click on the cell to start copying in the data? Selection.TextToColumns Destination:=3DRange("A1"), = DataType:=3DxlFixedWidth, _ FieldInfo:=3DArray(Array(0, 1), Array(41, 1), Array(82, 1), = Array(90, 1), Array(131, 1), _ Array(143, 1), Array(169, 1), Array(191, 1), Array(203, 1), = Array(216...

make excel files with same names to be opened at one time
It is very difficult to work if you want to compare changed data made in the file with old one ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=cde27763-1bc7-4299-874f-ac53e066ae7a&dg=microsoft.public....

Opening a a a file handle as a file pointer
Hi all I have a file handle. I want to open it as a FILE stream so that I can use higher level operators like cin/cout and fprintf/fscanf. Can someone suggest me a solution? Obviously the process startup code converts the file descriptor is receives from parent for stdin,stdout and stderr. So there should be a function to do it. regards, Sudhakar "sudhakarg79" <sudhakarg79@discussions.microsoft.com> wrote in message news:ECF7527B-A036-48EF-B2FB-CA8985330D3E@microsoft.com... > > Hi all > > I have a file handle. I want to open it as a FILE stream so...

Tidying up Office Data Files
My Office Data files are in a mess. I have discovered that I have Outlook (approx 0.8GB) and Outlook1 (approx 0.8GB) Mailbox (32KB) and Archive (0.35GB). I created Archive some time ago but have not archived recently. I don't know why I have 2 Outlook files - possibly created when I transferred data to a new laptop a few weeks ago. I would like to re-combine everything into 1 file so that I can start again and set up a proper Archive system again. How can I do this without risking losing files or creating duplicates or triplicates. As you will note the files are very big! Any h...

Closing multiple files independantly
When I have 2 seperate excel files open...and I click on the red 'x' on the top right on one of the files...both files close. How can I get it to only close the 1 file I'm clicking on? Thanks, Frank. Hi Frank, Look just below the red X; the three symbols (miminize, restore and close) are repeated but in a simpler format. These control a workbook while the top one control the Excel application. If you ever want to close all the file but leave Excel open, hold down Shift and open the File menu - look for a new item "Close All". Bernard "Frank DAlessandro" ...

quotation marks after import
I imported my contacts from outlook 2003 into entourage. After import, the data under each field name has "quotation marks" . Is there something I missed in the mapping, etc.? You didn't do anything wrong. This is an unfortunate annoying side-effect of directly transferring information from Outlook into Entourage. Paul Berkowitz's "Export-Import Entourage" script set will transfer data between Outlook & Entourage, taking care of these issues. <http://www.scriptbuilders.net/> On 12/27/05 6:02 PM, in article 1135728164.100437.84780@g43g2000cwa.googlegro...

an outlook2000 macro programming question.....
an outlook2000 macro programming question..... I created a user-defind field "price" in outlook2000 "task", I want to count the all item of "price" field, and I know Excel can do this perfect, but when I export outlook "task" to an Excel file, the "price" field can't be converted , Outlook2000 can't convert user-defined field, so I try to find another way to do this statistic work, anybody know if Outlook2000's macro programming can do this? ...

Macro & Protect Commands
I am having a problem with the excel 'protect sheet' and 'macro' functions in Excel 2002. For some reason they are greyed out (not available) in the tools pull down menu. I have already tried the macro security settings and that is not it. Some additional information: I am not able to right-click on the worksheet tabs and if I log in under a different user name the functions are available. I have tried reinstalling office and it didn't help. I know it must be something in my user settings but I am not aware where to go to change settings concerning Excel. A...

Compress an Excel File
I have an excel file that is 36 meg. Do you know how to compress the file? When I zip the file it drops to 3meg so I know it can be compressed. I beleive the file has become large due to the constent changes I need to make to the file. Help Please. Hi Mandy Maybe this will help? http://www.contextures.com/xlfaqApp.html#Unused You can use Rob Bovey's Code cleaner to clean up your code http://www.appspro.com/utilities/utilities.asp -- Regards Ron de Bruin http://www.rondebruin.nl "Mandy" <mangelo@arbys.com> wrote in message news:42c401c47fa1$ddc405d0$a401280a@...

my file still exists but is blank please help
I had a virus completely delete a file in my microsoft word 2000. It's old and I don't know what to do. The file name is still there but the text is blank, and I've tried several recovery progrms that haven't retrived it. Was hoping someone knew if word has its own back to retrieve things from and how to use it If the contents have been overwritten (and it certainly seems as if that is the case), there isn't anything you can do, I'm afraid. -- Stefan Blom Microsoft Word MVP "hanna" <hanna@discussions.microsoft.com> wrote in messa...

cannot install outlook from outlook express (import stalls)
trying to convert from express but installation fails from start. other office features do function ie excel and word In news:E167009C-F5CD-4D0F-B08B-217C0FFE3E78@microsoft.com, outlook fails to install <outlook fails to install@discussions.microsoft.com> typed: > trying to convert from express but installation fails from start. > other office features do function ie excel and word Install and configure Outlook on this computer. Then, launch OE and *export* from there to Outlook.....do not *import*. ...

Diable excel macro for DATES
Hi, everytime i copy and paste from a website in this format XX/YY excel sees it as a DATE. how can i disable this feature..I just want t paste it as its raw form of XX/YY. When i cut and paste it in my exce and try to change the format by using TExt. it comes up with rubbis numbers. Thank -- Message posted from http://www.ExcelForum.com try formatting, as text, before you import -- Don Guillett SalesAid Software donaldb@281.com "evo >" <<evo.18vufd@excelforum-nospam.com> wrote in message news:evo.18vufd@excelforum-nospam.com... > Hi, > > everytime i copy ...

Public Folder File sizes
Hi We have a system whereby files from our students are routed to a public folder, so that everyone in the office can see them. However some of our more unenlightened students seem to be trying to send in emails with 9Mb attachments. (AGHHHH) The odd thing is that if they send them to a persobal mailbox, they seem to get thropugh, but if theu send them to a public folder, Exchange cant handle them, or at least we can see them being received, but then they are not delivered to the public folder. Is there something i need to do to get round this please? Thanks A Do you have Maximu...