Blank lines on line input

Hi I'm using a recordset to read a text file and input the data into an
access 2000 table.  My problem is I want the line input to treat a blank
line as eof, there is information after the blank line but I don't need to
import it.
This is my code

Function ap1()
   Dim Filepath As String
   Dim Currentfile As String
   Dim rs As DAO.Recordset     ' Database
   Dim fh As Integer           ' File handle
   Dim lc As Long              ' Line counter
   Dim ln As String            ' Actual line
   Dim sp As Long              ' Position of first space
   Dim thefile As String       'full filename

   Filepath = "C:\temp\*.PO"
   Currentfile = Dir(Filepath)
   thefile = "c:\temp\" & Currentfile

   Set rs = CurrentDb.OpenRecordset("TblAllpay", dbOpenDynaset)


   fh = FreeFile
   lc = 0
   Open thefile For Input As #fh
    Line Input #fh, ln

   Do While Not EOF(fh)

      lc = lc + 1

        sp = InStr(ln, " ")

         rs.AddNew
         rs!Acc = 0 & Mid(ln, 9, 7)
         rs!Transdate = Right(ln, 10)
         rs!Amount = Mid(ln, 16, 16)
         rs!Details = Mid(ln, 2, Len(ln))
         rs!apfn = Currentfile
         rs.Update

   Loop
   Close #fh

End Function

Thanks

Wendy


0
Wendy
5/24/2007 3:19:37 PM
access 16762 articles. 3 followers. Follow

1 Replies
676 Views

Similar Articles

[PageSpeed] 43

Use: Exit Do

Example: If rs!Acc is empty, exit loop:
    If Len(nz(rs!Acc,""))=0 then Exit Do

The condition can be as simple or complex as you want.
Program control immediately jumps to the command following Loop (i.e., Close 
#fh)

HTH,



"Wendy" <Wendy@sunnygirl.com> wrote in message 
news:4c-dnXQZzPbwMMjbRVnytAA@bt.com...
> Hi I'm using a recordset to read a text file and input the data into an
> access 2000 table.  My problem is I want the line input to treat a blank
> line as eof, there is information after the blank line but I don't need to
> import it.
> This is my code
>
> Function ap1()
>   Dim Filepath As String
>   Dim Currentfile As String
>   Dim rs As DAO.Recordset     ' Database
>   Dim fh As Integer           ' File handle
>   Dim lc As Long              ' Line counter
>   Dim ln As String            ' Actual line
>   Dim sp As Long              ' Position of first space
>   Dim thefile As String       'full filename
>
>   Filepath = "C:\temp\*.PO"
>   Currentfile = Dir(Filepath)
>   thefile = "c:\temp\" & Currentfile
>
>   Set rs = CurrentDb.OpenRecordset("TblAllpay", dbOpenDynaset)
>
>
>   fh = FreeFile
>   lc = 0
>   Open thefile For Input As #fh
>    Line Input #fh, ln
>
>   Do While Not EOF(fh)
>
>      lc = lc + 1
>
>        sp = InStr(ln, " ")
>
>         rs.AddNew
>         rs!Acc = 0 & Mid(ln, 9, 7)
>         rs!Transdate = Right(ln, 10)
>         rs!Amount = Mid(ln, 16, 16)
>         rs!Details = Mid(ln, 2, Len(ln))
>         rs!apfn = Currentfile
>         rs.Update
>
>   Loop
>   Close #fh
>
> End Function
>
> Thanks
>
> Wendy
>
> 


0
George
5/24/2007 4:29:33 PM
Reply:

Similar Artilces:

How should i input installments of credit card payments?
Hi, I have bought something with my credit card in 24 installments (interest free), the total sum is $1200 and i would pay $50 each month, how should i input these data in Money? Thanks! You input a charge in your credit card account of 1200 dollars and schedule a bill transfer of 50 dollars each month from the current account to the cc account. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically asked for. "Lendsly" <Lendsly@disc...

Putting Excel on-line interactivly
I know how to put an Excel sheet on line so that the user can do calculations with it, but I want the user to be able to modify the sheet so that it stays modified. I suspect it's a cgi bin activity, but I have no experience of interactive web sites. If anyone can point me to a knowledgable source of information, I'd be very greatful. ...

trend line #3
1) can we create a trend line from equation which has 2 variables and 1 respond ? and How? (Ex. Y= 1.2+5A+3B) 2) In case that we have two trend lines ( from X1 with Y and X2 with Y) and we want to create the average line between those two lines, what should we do? Thank you I'm not following what you are asking. Can you be more specific on the data you have and the trends you want? "Nee" <Nee@discussions.microsoft.com> wrote in message news:15EE50FC-4CA7-43F6-A111-CAB2CB899E4C@microsoft.com... > 1) can we create a trend line from equation which has 2 variables an...

Grid lines not printing #2
Got a weird one here--One of my users is trying to print an Excel spreadsheet and the grid lines are not printing as they should. They print properly from other machines for the same document. I cannot find any settings in options to correct the problem. Any ideas? Have you tried: File - Page Set up - Sheet - Gridlines From the menu bar. Good luck Mike >-----Original Message----- >Got a weird one here--One of my users is trying to print >an Excel spreadsheet and the grid lines are not printing >as they should. They print properly from other machines >for the sam...

Time allotment deduction in Contract Lines
Hi, I tried to use the contract in CRM to track SLA activities. I created a new template and set "Allotment Type" to "Time". However when I created a case with a service activity and completed (or resolved) both, the deduction was only 1 instead of actual minutes set in the service activity. I set the minutes of Contract Line to 120 and task took 1 hour (60 minutes). There has been some customizations and workflow done for Cases, not Contract. But I wouldn't have thought that would have mattered. Any enlightment is appreciated. Tony Hi Tony, I just tried the...

input box upon workbook open
I want to have an input box that will ask for a date when a workbook is opened. I want to be able to us the date that is input in code that will be run from a macro. The reason I want the input box upon open instead of when the macro is run, is because the end user uses the file repeatedly for multiple calculations. This date will be applied to all of the calculations, and I don't want the user to have to enter the same date over and over. I assume that I need something like a "public enddate as date" in the ThisWorkbook object. This however doesn't work. What do I need ...

Blank page prints in between mail merged document
I have a word template and an access database file used to create mass mailings. When completing the mail merge, I edit the mail merge recipients and merge to new document. The first page has my letter with a Section Break (next page) at the bottom, the second page has my letter with a Section Break (next page) at the bottom etc. When I print the document a blank page comes out between my letters. In the template there is not a section break at the end of the page. How can I stop the blank pages in between each of my letters? Also, is it possible to then use the same e...

Links error when insert line/row to source workbook
I have a question regarding links on Microsoft Excel 2003. If I insert a line into the source spreadsheet, the link on the destination spreadsheet doesn't work anymore because the cells moved. The scenario: On the destination spreadsheet, the link is =[ACME.xls]Sheet1'!A1 But if I insert a line on ACME.xls, making the info in cell A1 move down to A2, the link on the destination spreadsheet still pulls from cell A1. How do I ensure that when I insert/delete lines/row, the link still pulls info from the correct cell? Thanks Stella If you have the workbook with the fo...

Not able to add or edit records from an input form.
Hi, I have an MS Access input form that is based on a query that is based on a table called Training. The Training table is actually an SQL Server table that is linked. There are three fields in the table: ID (The primary key), CustomerName and TrainingDate. I added three records into the table using SQL Server Enterprise Manager and can add more. However, I can't add them using my access form. I can view them but cannot add. I can't edit them either. What setting do I make to allow edit and adding to occur? I posted this question in the sql server forum and was ...

How to add Upper/lower control limit lines to a graph (not drawn)
I have several quality charts that I do monthly. How can I get the the upper, lower and center lines to appear at a specific data point (number) as part of hte chart format. In other words, withiout having to use the Draw Line tool each time? Excel 2003. Fake it. In other words, seutp some "dummy" series that are calculated, and plot them on your chart. Format them to look however you like, and remove the original chart gridlines. If you have a legend, single-click twice the legend entry for your dummy series and press delete. If done correctly, the entry will not...

The blank cells i need to count have formilas in
How do i write a fromula to add together diffeent ranges of cells that will count blank cells even if the blank has a formula in it. f 17:f12 + g23:g30+h5:h10 = 8 using excel 2007Vista Hi Rubber, If the blank cells with a formula in them has the value of zero, use COUNTA to count the cells and COUNTIF to count the zero values in each range, then subtract the two. =COUNTA(F12:F17)-COUNTIF(F12:F17,0) or fully =COUNTA(F12:F17)-COUNTIF(F12:F17,0)+COUNTA(g23:g30)-COUNTIF(g23:g30,0)+COUNTA(h5:h10)-COUNTIF(h5:h10,0) Squeaky "Rubber 4 u" wrote: > How do i w...

View Additional Mailboxes Off-line
A user's Outlook profile has been configured to "Open these additional mailboxes." She is able to access the additional mailboxes while online. She does a "send / receive" to synchronize her mail and then goes off-line. When off-line, she can view only her primary mailbox. She cannot access the additional mailboxes. Her OST only includes her primary mailbox. She is using Outlook 2003, Exchange Server 2003. Outlook is using "cached" mode to connect to the Exchange server. Is this working as designed? Is there a way to view additional mailboxes off-line (wi...

can you force long lines of text to wrap width of cell?
should i do nothing or choose the wrap text option in the format cells dialog or click wrap text in the edit menu, or right click the cell and select wrap text help? Wrap text in the Format Cells dialog -- HTH RP "excel help" <excel help@discussions.microsoft.com> wrote in message news:18081AAF-FD5E-4FF4-BF77-EDFF43B74901@microsoft.com... > should i do nothing or choose the wrap text option in the format cells dialog > or click wrap text in the edit menu, or right click the cell and select wrap > text help? ...

create chart from daily data and add trend lines for each week
Excel 2003 Is there a way to create a chart using daily data and create a trend line for each week. The data does not have an Saturday's or Sunday's. My data looks like this. Date Output Input 2/15/2010 229 215 2/16/2010 441 153 2/17/2010 205 43 2/18/2010 283 138 2/19/2010 609 121 2/22/2010 272 136 2/23/2010 373 16 2/24/2010 193 176 2/25/2010 339 358 2/26/2010 533 238 3/1/2010 374 252 3/2/2010 244 269 3/3/2010 306 216 3/4/2010 297 229 3/5/2010 538 202 3/8/2010 235 373 3/9/2010 446 410 3/10/2010 385 335 3/1...

How can I create a new line in a cell in Excel by hitting enter?
I need to just be able to hit enter in a cell and it create a new line in that cell. Is there a way to do this so that I do not have to add more rows? Please help! Manual (forced) line break: <Alt> <Enter> -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Monica" <Monica@discussions.microsoft.com> wrote in message news:A711F1A5-5C6A-4547-BC55-4E1FAC262B91@microsoft.com... I need to just be able to hit enter in a cell and it create a ...

why does deleted email have a line thru it
I have 2 email accounts. One works fine. On the other; when I delete an email I have a line thru it and the email stays in the inbox and cannot be removed. Help. Did you try and hit Send\Receive when the line is through the message. Does it delete then -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "Michael" <Michael@discussions.microsoft.com> wrote in message news:AFDC8DB7-12D5-47C3-AA1E-74F72CF0258D@microsoft.com... >I have 2 email accounts. One works fine. On t...

Eliminating Blanks
Happen that I downloaded a huge list of items from a database, somehow the list is coming with blank spaces after the item and when I try to match the items with another list it can not find it, could somebody help me to see if there is a formula or way to eliminatesblank spaces after the las digit of the item number: I.E. R1J150 End of list downloaded R1J150End of my other list. Thanks in advance for your help Fernando Gomez Fernando Depending on the layout and content of your data, you could use Find/Replace to replace the spaces with nothing! Andy. "Fernando Gomez" <...

Question understanding a line of code
hi - I'm working through examples of code, learning what I can. In Allen Browne's web site, there is a 'Function ShowFieldsRS(strTable)'. I'm trying to understand the purpose of the 'False' in the code fragment below. strSql = "SELECT " & strTable & ".* FROM " & strTable & " WHERE (False);" The code works perfectly, but I don't understand why. I would have thought that 'WHERE(False)' would allow no records to be chosen. just wonderin' Victoria "Victoria" <Victoria...

Inserting default value in part of input mask
I have a DB where user enters CourseName and Then CourseNumber (Primary key and Unique).. I run a code to check and display a custom error message if the value in CourseNumber is duplicate. The CourseName are actually abbreviations like AFF or PSCRB, or MFA etc... CourseNumber contains three parts i.e. CourseSerialNumberInCurrentyear +CourseName+last2digitsofCurrentYrneeds for example 001AFF09 or 029MFA09 or 010MFA10.. because of different user using the db they enter the same course no. in different formats like 001AFF09 or 001AFF2009 or 001/AFF/09 or AFF/ 001/09 and so many w...

Pivot Data Field Blanks
I would like a Pivot table with data in all fields all rows even if the first column is repeated. I now have the following result: Company City Country AAA Manchester UK Manchester USA What I want is: Company City Country AAA Manchester UK AAA Manchester USA Any ideas? You can't do this and and keep it a pivottable. You could convert it to values (select, edit|copy, edit|paste special|values) (or do the same thing against a copy) Then use some of the techniques at Debra D...

multiple inputs into same cell and totaling
I have A as constant. I subtract B from A giving me C. I want to be able to input different values(numbers) into B that accumulate. So 1st input 10 giving C new value. 2nd Input of 5 in B and having B become 15 which than updates C. So A's value 100 Subtract B's value from A. Example 100-10 Giving C's value 100-10+90 Than input new value into B: 5 which adds to B original 10 making it 15 C's value should than become 85. I want to be able to input new value in B as many times as I want. -- Atienne This example uses A1, B1, and C1. Put the following event macro in the w...

command line argument is not valid
Hello All; When I try to open an attachment in OutLook(Office XP Suite)I recieve the following error message. "Microsoft Office Outlook. The command line argument is not valid. Verify the switch you are using." Now these attachments are Office Suite documents. Attachments such as PDF's and self-extracting exe's work just fine. I have searched the KB at Microsoft and Googled it. Have found nothing clear on the subject. Has anybody see this? ...

Lines Between Columns
Publisher 2003 I have a newsletter page that is formatted to be double column. I want to place a 0.5 point line between the columns. Right clicking within the double columns then clicking on format text box>colors and lines then clicking the box in preview that is for the center line only nothing happens to the menu on the left. The "dashed", "style" and "weight " boxes are grayed out. Out of the six boxes shown there, the only two that activate the above quoted boxes are the two on the right of the bottom row (frame and frame with line down the middle). I ...

Adding a line
I am very new to Excel, but I do pick up things pretty quick. I have started a chart with a list of my customers and it is working out really good. My question is I need to add another name/line in between the others. Meaning I need to put someone on line 18 but I already have people on there up until 32. I don't want to cut and paste everyone from 18 down just to move them down one. Is there a way I can move them down all together? Thank you so much for the help. I hope I explained this good enough. Elaine [This followup was posted to microsoft.public.excel.charting with an ema...

CFile removing a line and replacing it
Hi Guys, This is a wood for the trees question I'm sure. I have a problem that when I open my text file I need to read the last line, then replace it with something else. How do I achieve this? TIA Tony Tony, There are many ways to do this. I would have used a CStdioFile, read all the lines into a CStringArray, replaced the last string and written it back again. You might also want to look at CFile::SetLength to truncate a file. Johan Rosengren Abstrakt Mekanik AB "Tony" <lazyherbert@homeandresting.com> a �crit dans le message de news:%23LXr8rieEHA.644@tk2msftngp...