asked on

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

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Nick67

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mytfein

ASKER

Hi Graham and Nick,

Thx so much for writing....

I understand what you both wrote....

Thx again for your help, sandra

mytfein

ASKER

Dear Public,

Both solutions are the best solution :-)

mytfein

ASKER

Hi Gentlemen,

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

https://www.experts-exchange.com/questions/28683485/Word-2010-want-to-make-some-changes-to-the-doc-as-a-regular-User.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

Nick67

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