Access 2010 executing Excel VBA: need help understanding code gotten in the past from EE

Hi EE Experts,

In the past an EE expert provided logic to import a .txt file into an Excel spreadsheet

 (that can be imported in the next step into Access)

Am pasting the code snippet below:

It's been awhile since I looked at this code and need help understanding the following lines:

Lines:
======

If aline$ <> "" Then objExcelActiveWs.Cells(r, c) = ColType(c) + Trim$(StripQuotes(aline$))
       
       r = r + 1


Questions:
==========

1) is the r=  r+1 part of the IF statement
   or does and if statement without an END IF end on same line the IF is written

You see I like putting END IFs in code so is this ok:

If aline$ <> "" Then
     objExcelActiveWs.Cells(r, c) = ColType(c) + Trim$(StripQuotes(aline$))

END IF

r = r + 1

2) What is the IF statement doing, not understanding

tx so much, sandra


Code snippet:
============
'open .txt file and place into a blank Excel worksheet, one row at a time

Open strInputFileName For Input As lngFileNum

    Do
        Line Input #lngFileNum, aline$
        lngDelimPos = InStr(aline$, strDelim)
       
        While lngDelimPos > 0

' fill row in excel, horizontally one column(field) at a time
             objExcelActiveWs.Cells(r, c).Value = _
                              ColType(c) + StripQuotes(Left$(aline$, lngDelimPos - 1))

            aline$ = Mid$(aline$, lngDelimPos + 1)
            lngDelimPos = InStr(aline$, strDelim)
            c = c + 1
        Wend
       
       
        If aline$ <> "" Then objExcelActiveWs.Cells(r, c) = ColType(c) + Trim$(StripQuotes(aline$))
       
       r = r + 1
       
             
       c = 1
    Loop Until EOF(lngFileNum) Or r > lngRowMax
   
Close lngFileNum


With objExcelActiveWs
         
     With .Cells
          .Select
          .EntireColumn.AutoFit
     End With

End With
mytfeinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
The If statement ends with the line if the 'True' action is on the the same line, so no EndIf is needed, and the r=r+1 will happen when the condition is met or not.

Note, that the '+' acts like a '&' when the operands are strings. This is a hangover from VB1.

So:
objExcelActiveWs.Cells(r, c)  will contain the concatenation of ColType(c) and  Trim$(StripQuotes(aline$))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Commented:
Commented
'open .txt file and place into a blank Excel worksheet, one row at a time

'open the file
 Open strInputFileName For Input As lngFileNum

     Do 'loop to walk through each line in the txtfle
         Line Input #lngFileNum, aline$ 'grab a line
         lngDelimPos = InStr(aline$, strDelim) 'find the first delimiter -- presumably a comma
         
         While lngDelimPos > 0 'now a loop to blow what's between each delimiter into a column

             ' fill row in excel, horizontally one column(field) at a time
             'add a value to the present row/column location
             objExcelActiveWs.Cells(r, c).Value = ColType(c) + StripQuotes(Left$(aline$, lngDelimPos - 1))
             'adjust the string to eliminate the value and delimiter just dealt with                
             aline$ = Mid$(aline$, lngDelimPos + 1)
             'check that there will be another delimiter in this line
             lngDelimPos = InStr(aline$, strDelim)
             'advance the column to the next column
             c = c + 1
         Wend 'loop until the line no longer has an undealt-with delimited         
         'with no delimiter left, we have the final value in the line--add it to the last column
         If aline$ <> "" Then objExcelActiveWs.Cells(r, c) = ColType(c) + Trim$(StripQuotes(aline$))
         'this row is done.  Advance the row counter        
         r = r + 1
         'reset the column counter       
         c = 1
     Loop Until EOF(lngFileNum) Or r > lngRowMax 'keep looping until the file has no more lines
     
'close the file 
Close lngFileNum


'autofit the results
 With objExcelActiveWs          
      With .Cells
           .Select
           .EntireColumn.AutoFit
      End With
 End With 

Open in new window

mytfeinAuthor Commented:
Hi Graham and Nick,

Thx so much for writing....

I understand what you both wrote....

Thx again for your help, sandra
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

mytfeinAuthor Commented:
Dear Public,

Both solutions are the best solution  :-)
mytfeinAuthor Commented:
Hi Gentlemen,

In case you have time, I posted a related question from the Word side, here:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/Q_28683485.html

This is not a VBA question, just how to use Word to adjust margins.....

It's somewhat related to this post because it is the pdf documention (converted to word)

The fields in the text file have no field names, in the Excel VBA code, we give the fields, field1, field2, field3 etc

So in the word doc that uploaded want to add to the table as the first column a field number such as 1,2,3 going downward vertically

This way it will help me understand the fields in the text file, and if I go on vacation my colleagues can understand as well.

tx, sandra
Nick67Commented:
I don't really understand.
In this Excel file it would be child's play to amend the loop so that the first column contains a 'row number'

            ' fill row in excel, horizontally one column(field) at a time
             'add a value to the present row/column location
             objExcelActiveWs.Cells(r, c).Value = ColType(c) + StripQuotes(Left$(aline$, lngDelimPos - 1))

Becomes


             ' fill row in excel, horizontally one column(field) at a time
             if c = 1 then
                  'add the row number to the first colum
                  objExcelActiveWs.Cells(r, c).Value = r
                  'go to the next column
                   c = c + 1
             End If
             'add a value to the present row/column location
             objExcelActiveWs.Cells(r, c).Value = ColType(c) + StripQuotes(Left$(aline$, lngDelimPos - 1))
'.....rest of the code

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.