Microsoft Exsl 97 Visual Basic by Reed Jacobson 3

[LouLBI]

I am using this as aself teaching text. I find it hard to follow and if I make an error it is difficult to recover. Is anyone familiar with this book?

 

[SkipVought]

Hi LouLBI and welcome to Tek-Tips.

Please post specific questions along with the code you are using, a description of any data and the results you get.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LouLBI]

In trying to reproduce lesson 2 p 33, I write
Sub ImportFile()
'
' ImportFile Macro
' Macro recorded 5/17/2012 by Louis Napoli
'

'
    myFile = Application.GetOpenFilename("Text Files.*.txt")
    Workbooks.OpenText
        FileName:=myFile, _
        Origin: = xlWindows,_
        StartRow:=4, _
        DataType:xlFixedWidth, _
And I get an error message "compile error" highlighting :=
Yet this is exactly what the book shows.

 

[combo]

You should learn vba syntax. Still heppful could be vba help (conceptual topics) in the vbe environment.
There are a lot of syntax errors in your example, be careful:
- [tt]Workbooks.OpenText[/tt]: a line divisor missing, the following (named) arguments should be in one command,
- [/tt]Origin: = xlWindows,_[/tt]: a space is missing before line divisor,
- [tt]DataType:xlFixedWidth, _[/tt]: command divisor ) instead of named argument assignment =).

Be careful with excel version you use. VBA in excel 97 was based on visual basic 5, VBAs in office 2000 and later are based on visual basic 6. Additionally, host application's object model (in your case excel and could be office) varies with office version. By theory all the code should work when you upgrade office, but it's not so.

combo

 

[LouLBI]

This is what i get when i run the routine
Sub ImportFile()
'
' ImportFile Macro
' Macro recorded 5/17/2012 by Louis Napoli
'

'
    Workbooks.OpenText Filename:="C:\Excel VBA Practice\Ord9711.txt", Origin:= _
        437, StartRow:=4, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
        Array(8, 1), Array(20, 1), Array(26, 1), Array(41, 1), Array(49, 1), Array(59, 1), Array(67 _
        , 1)), TrailingMinusNumbers:=True
    With ActiveWindow
        .Top = 9.25
        .Left = 166
    End With
    Sheets("Ord9711").Select
    Sheets("Ord9711").Move Before:=Workbooks("Lesson2.xls").Sheets(1)
    With ActiveWindow
        .Top = 34
        .Left = 15.25
    End With
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
End Sub

This is what the book shows
NnSub ImportFile()
'
' ImportFile Macro
' Macro recorded 11/16/96 by Reed Jacobson
'

'
    Workbooks.OpenText _
    Filename:="C:\Excel VBA Practice\Ord9711.txt", _
    Origin:=xlWindows, _
    StartRow:=4, _
    DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 1), Array(8, 1), _
        Array(20, 1), Array(26, 1), Array(41, 1), _
        Array(49, 1), Array(59, 1), Array(67, 1))
    With ActiveWindow
        .Width = 452.25
        .Height = 254.25
    End With
    Sheets("Ord9711").Select
    Sheets("Ord9711").Move _
        Before:=Workbooks("Lesson2.xls").Sheets(1)
    Range("A2").Select
    Selection.EntireRow.Delete
    Range("A1").Select
End Sub

I see similarities, but don't understand the differences. Is their a significant difference?

 

[SkipVought]

If formatted the code in the editor to have one argument per row and pasted each into an Excel sheet and then used a formula to test each row for equality.

You can see the differences this way.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

VBA is rather descriptive. In this example the differces are caused either by different user actions recorded (for active window, cell/row selection) or guess that different excel versions (Origin argument for TextOpen method).
You could copy complete blocks of the code, next paste and execute in separate procedure. IMHO the fundamental problem is to understand what the code does rather than focusing on differences.

combo

 

[LouLBI]

Is there a "Rosetta Stone" type reference that shows what each statement does?
Sorry that I don't know what IMHO stands for.

 

[SkipVought]

If you Google IHMO, you can answer your own question.

VB Help can give you such information.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

"what each statement does?"
If you know how to set a break point in your code (if you don't - you should ): place the cursor on the line you want to stop at and hit F9, and when you excecute your code and stop on that line, hit F8 to step line-by-line thru your code. You can see what every line of your code does.

If works great if you have 2 monitors, but one is fine, too.

Have fun.

---- Andy

 

[LouLBI]

Sub FillLabels()
'
' FillLabels Macro
' Macro recorded 5/18/2012 by Louis Napoli
'

'
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
    Selection.CurrentRegion.Select
    ActiveWindow.SmallScroll Down:=1
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
End Sub

I created the above macro, but when I stepped through it using F8, it got hung up on
Selection.SpecialCells(xlCellTypeBlanks).Select
Showing a "run time error '1004'and no cells found. Any thoughts on that?

 

[xlbo]

means there is no blank cells in the range you have selected
That is why most people that program with excel do not use select / activate as it can cause problems with understanding the range of cells you are working with. Unfortunately, recording a macro DOEs use a lot of select / activate

As a basic tip, if you see a select at the end of 1 line followed by a selection at the start of the next you can dispense with both

So:
Range("A1").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

Can become:
Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

Please feel free to use the VBA help to try to understand basic error messages


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question