Regular data file imports

We have a General Ledger file that is beining imported by Users on 
dialy basis into excel from a text file.

The column widths are identical every day.

Currently the Import wizard is used on a daily basis to split the dat
into the selected columns.

How can I have the file automatically complete the format when the fil
is opened or after the file is opened.
The file will have a different name each day.

Is there an easy way or will a macro have to be written.

Thanks

ca

--
Message posted from http://www.ExcelForum.com

0
9/13/2004 10:45:10 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
551 Views

Similar Articles

[PageSpeed] 49

I receive a 4,000+ line bank statement each month in text format and have
recently automated the import of it into a 5 sheet setup; It is great.  Use
the macro recorder as I did (turn it on) and Step through the File, Open
(target your *.txt file) and the Import Wizard should take over - carefully
(take your time) set everything up as you want it, when finished Stop
Macro - You are 90% of the way at this point -- the main single line of code
the recorder will create will look something like this (I'm using Excel
XP)..

Workbooks.OpenText Filename:= _
        "C:\My Documents\MyCash\MyBank.txt", Origin:= _
        437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0,
9), _
        Array(13, 2), Array(23, 1), Array(33, 5), Array(41, 5), Array(49,
2), Array(50, 2)), _
        TrailingMinusNumbers:=True

You can customize the Code (with the help of this group).  Good Luck,
Jim May

"nzcam >" <<nzcam.1cipl8@excelforum-nospam.com> wrote in message
news:nzcam.1cipl8@excelforum-nospam.com...
> We have a General Ledger file that is beining imported by Users on a
> dialy basis into excel from a text file.
>
> The column widths are identical every day.
>
> Currently the Import wizard is used on a daily basis to split the data
> into the selected columns.
>
> How can I have the file automatically complete the format when the file
> is opened or after the file is opened.
> The file will have a different name each day.
>
> Is there an easy way or will a macro have to be written.
>
> Thanks
>
> cam
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
jmay (696)
9/13/2004 11:24:27 AM
Reply:

Similar Artilces:

Catching up with old 401 k data ?
I stopped managing my Money file, other than simple checking & credit card reconciliation, when our twins were born. Now, nearly five years later, I am scratching my head, trying to figure out the best way to catch up with 401k accounts. We've moved money around, changed funds & distributions, my wife switched jobs, etc. I have two goals - first, just get everything up to date as it is now (I understand this would screw up my cost basis). Second, I would like to, eventually, go back & enter prior years. What is the easiest - or best - way to handle the first task, without scr...

import messages and contacts from yahoo mail to outlook 2000
I am shifting email from yahoo to outlook 2000, can I export all my current yahoo messages, folders and contacts to outlook before dropping yahoo? "michael" <michael@discussions.microsoft.com> wrote in message news:D11CA77B-513E-4647-94BF-CBA7897B7E44@microsoft.com... > I am shifting email from yahoo to outlook 2000, can I export all my > current > yahoo messages, folders and contacts to outlook before dropping yahoo? Dunno, have you looked in Yahoo help, perhaps? ...

Settings margins for imported documents
When I import a document from Word into Publisher I find I am unable to change the margins on the pagfes. The document has 108 pages. I have tried to change the Master and apply it to all the pages but it just does not work. Any help would be appreciated. Thanks How are you importing? Best way to import text would be to create two pages in Publisher, on the arrange menu set your margins, create a text box on each page, link them, copy the text from Word, paste into the first page, Publisher will inquire about creating pages, click yes... -- Mary Sauer MS MVP http://office.microsof...

missing data from dropdown column
I have a dropdown list. On one pc the first name column does not display any data. All other pc's display data. I seem to recall a fix for this on ms website. Can anyone help? TIA. patti "patti" <patti@discussions.microsoft.com> wrote in message news:32D8FF14-7B6F-400F-9EE0-D04435D4EF7F@microsoft.com... >I have a dropdown list. On one pc the first name column does not display >any > data. All other pc's display data. I seem to recall a fix for this on ms > website. Can anyone help? Possibly this is the problem: http://support.m...

Access 2007 Excel Import Error
I'm trying to import an Excel spreadsheet into Access 2007 and keep getting "The search key was not found in any record." error message. I have deleted all blank rows, columns, tabs, formatted all the data to match Access, etc. What is this "search key"? -- Thanks! On Apr 27, 10:10=A0am, rba1012 <rba1...@discussions.microsoft.com> wrote: > I'm trying to import an Excel spreadsheet into Access 2007 and keep getti= ng > "The search key was not found in any record." error message. =A0I have de= leted > all blank rows, columns...

New Vista .docx files
Does anyone know where I can get the file to convert the new .docx files? At www.mactopia.com you will see "Quick downloads" on the right. Under that: "Microsoft Office Open XML File Format Converter for Mac 0.2 (Beta)". There are other solutions but I take it you're specifically after this converter -- which handles mst, but not all, Word 2007 formatting. Cheers, Clive Huggan Canberra, Australia (My time zone is 5-11 hours different from North America and Europe, so my follow-on responses to those regions can be delayed) =========================================...

Autofill data
Hi I am new to excel and have a question regarding cells being filled with data automatically If I want to have a cell that contains a product, how do i get the cell next to it to fill in the price automatically - I guess i am going to have to have a set of prices allocated to a product somewhere in the sheet so it calls the info in from this list? Hope you kow what i mean regards mark Hi Mark Look in the Excel help for the Vlookup formula Post back if you need more help -- Regards Ron de Bruin http://www.rondebruin.nl "Mark Reynolds" <Mark Reynolds@discussions.mi...

Copy Paste of Filtered Data "Uses" too many Cells
Using XL 2003 & 97 Have about 8000 rows of data in 18 Columns. Using Autofilter, I filtered the data down to about 5000 cells After inserting a new sheet, and using VBA, I copy/pasted the filtered data to the New Worksheet. Then I noticed that the New WS had a used range of >65,000 rows by 18 columns. Of course this is not efficient. What is causing the excess cell "Used Range"? The code that I am using to copy paste follows: ......... Sheets.Add.Name = "New" Sheets("Filtered Data").Activate Cells.Copy Sheets("New").Act...

Deleting file and path
I have a form that is bound to a hyperlink table. I have a delete button with which you can delete the particular hyperlink record including the underlying file. The function that executes the code, first tries to delete the file. If that's successful, the hyperlink record is deleted. There could be situations where the hyperlink record can't be deleted for instance because it's locked. In that case the function would already have deleted the file, while the hyperlink record remains there. Is there a way I could perhaps 'pre-lock' the hyperlink record, so ...

Extract Repeat Customer Data
Hi. I have a list of customers and their purchases. Whenever a repea customer is entered, I would like to list the dates and values o previous purchases next to his latest entry. A simple record might look like this; 01/02/04 Customer A �20 02/02/04 Customer B �30 03/02/04 Customer C �25 04/02/04 Customer B �40 In this example the last entry is for Customer B, who is a repea customer. In the cells to the right of the value of his latest purchas I would therefore like to show the date and value of each of hi previous purchases. I...

Making a bar chart "broken" for large data
I have a bar chart I am making in which one series of data is much larger than the others (say 30,000 units versus 1,000 units). When I chart it I get one very long bar for the 30,000 units and a buch of small bars for the other series. I'd like to "break" the y-axis scale so that the 30,000 unit series is still shown but is not out of line with the others. It is a change in the graphical represnetation of the data, but I've looked in Help but can't seem to find this feature. It is hard to describe in text, but I've seen it done quite often in publication....

Regular Survey
I know I can do it individually or I could delete the whole survey but I currently have a regular monthly survey which I send out via MOSS which has over 100 responses and I don't want to delete one by one ready for next month. Any ideas? ...

enter data in cell which will start macro to move data to sheet2
I have two sheets, one sheet has name, score, handicap, net score. I have a macro that I run after I enter the new score and it updates sheet2. I would like to enter data on sheet1 and it auto update on sheet2. The column's are a, b, c, d. I update column b. When it updates it needs to take column (a) name, and column (b) score and update it on sheet2, with the lowest score first. Again I have a macro that I start and update after I've finished entering the scores, just looking for a quicker way to do this. Thank You ...

Scatter Chart Data Point Highlighting
I have a scatter chart with smooted lines and no data markers tha represents a (one) mathematical function. Is it possible to highligh (mark) one data point and from that point draw parallel (dashed) line to the x and y axes? By hovering over the chart you can pop up the data values but I wan (if possible) the parrallel lines to better visualize the position o the selected point on the axes -- rvExcelNewTi ----------------------------------------------------------------------- rvExcelNewTip's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1566 View this thread:...

view data from one table update and save to another table
I have a tabl do this without amending te with item data which I sow on a form to create a purchase order, where the item data may require amending before publishing to the supplier. How do I do this without amending original data which for audit purposes must remain as original. "Bloggsy" <Bloggsy@discussions.microsoft.com> wrote in message news:74D11113-3C3F-441F-8031-FA31035ED0B7@microsoft.com... >I have a tabl do this without amending te with item data which I sow on a > form to create a purchase order, where the item data may require amending > before publish...

Clear contents of last two rows of data on sheet
Hi. I'm using v 2003. I need a couple of lines of code that will delete or clear the contents of the last two rows on a sheet that have data in them. In other words, if there are 800 lines of data and I want rows 799 and 800 to be deleted or contents cleared. The number of the last row varies everytime I run the report. Can anyone help? Thanks! Dani Hi Dani, Here are 2 options. See the comments for when to use the option. 'Option 1 Sub DeleteRows1() 'When a specific column will always have data in last row. Dim lastRow As Long With Sheets("S...

XML heading on the Data menu in excel?
Mine is missing on my newly installed copy of MS Office Professional Enterprise Edition 2003. It is also missing the XML Schema tabs in Word! I need to know how i can find XML please help... email me at karenbutterfly@verizon.net thank you Karen If the XML item is missing from the Data toolbar you might try resetting your menus. Select View, Toolbars, Customize. On the Toolbars tab select Worksheet Menu Bar near the end and click Reset. -- Jim "karenButterfly" <karenButterfly@discussions.microsoft.com> wrote in message news:BFEA2D2A-3EB0-45CC-990E-F16BCBD2F965@micro...

Convert calendar data
I am trying to export my calendar data from one program into yahoo. Here is the current format layout in a csv file: Subject, Start Date, Description, Start Time, End Date, End Time. Here is the layout that I need it to be: Subject, Start Date, Start Time, End Date End Time, All day event Description. 1. I need to have the Description field moved to the end of th record. 2. The Description data from the current layout needs to be to be unde the Subject header not the Description. 2. The new All day event field needs to be added and populated wit "FALSE" 3. And I think each data ...

match computers in 2 text files
Can somebody help me with a vbscript. I want search computers in 2 textfiles, if the computers matched then write in Computers01.txt a text like "OK" i want read the first computers01.txt en match they other text file ( computers02.txt ) Example: computers01.txt computers02.txt ----------------- ----------------- server001,OK server003 server002, server001 server003,OK Server045 ...

No data in my journal!
I'm new to RMS and have recently installed our first customer with RMS 2.0 So far, we have gotten everything installed working to the customers liking minus a few things here and there. One of these things is that reprinting a receipt or reprinting from the journal doesn't seem to work. It contains no data in the journal to do any kind of reprint. Is this an option i have to enable to retain receipts in the journal? Yes - you need to check mark "journal receipts from this printer" in the receipt printer tab / tabs of your register - in SO manager go to database - regi...

launch app and read file by drag and drop
How do I get my application to open and read a file when the file's icon has been dragged onto the application icon? I can drop a file onto the open application and read it, but I can't figure out how the application gets the file name when the file's dropped onto the icon. I'm using VS 6.0. ...

Data validation
Can data validation be set up to accept a 0( (zero) or any whole number between 25,000 and 10,000,000? Or does this have to be controlled with VBA? This needs to work with xll03 and later versions This DV formula should work fine: =AND(ISNUMBER(A2),A2>=25000,A2<=10000000) Joy? hit the YES below -- Max Singapore --- "Brad" wrote: > Can data validation be set up to accept a 0( (zero) or any whole number > between 25,000 and 10,000,000? Or does this have to be controlled with VBA? > > This needs to work with xll03 and later versions > ...

Problem importing customization in virtual machine
Hi there I have a serious problem when it try to import a customization xml to a test environment of crm 3.0. The file has a normal size and is exportet from an nearly identical system whit very few and harmless difference. The upload of the file is no problem and alle the entities are displayed. When i to import all the customization or even a single entity the process starts and it seems to work fine. But it will never come to an end. Even if I way some 24 hour as I did the process will never be finished. When I look at the eventlog i have the following message appearing every 5-10 m...

Retrieving data from an inactive Registry
Hi - I recently reinstalled Windows XP clean on a PC, but I did it on a new hard drive. I have the PC's original hard drive, with information in its Registry files that I need to retrieve. How can I export data from these Registry files? Thanks CL Chuck Lavin wrote: > Hi - > > I recently reinstalled Windows XP clean on a PC, but I did it on a new > hard drive. > > I have the PC's original hard drive, with information in its Registry > files that I need to retrieve. > > How can I export data from these Registry files? Use ...

data range is too complex.
I am just changing over to excel 2007. . Most of my data will update to the embedded chart just fine but one tells me "the data range is too complex to be displayed. I don't know how to fix it -- Thanks for all your help. God Bless, Frank I had one like it and all I did to fix it was select the chart and right click on it, select data-> click on the icon at far right of Chart Data Range, use the mouse to select the data range, click on the icon at far right then OK. Note: If there are multiple ranges and the ranges are not adjacent, select the first range then hold the Ct...