running macro step by step different from normal run??

[Rinze Smit]

Hello everyone,

I have created this macro which opens all textfiles (one by one) in a
directory, and makes changes to the worksheet (mostly lay-out).
When testing the macro step by step (using F8) or using the 'play-button'
from VB-editor, the code (see below) converts the document in the correct
way. The original code was created using the macrorecorder.
However when I execute the macro, using a toolbarbutton, the data is NOT
imported in the right way.

Can anybody shine some light on this matter. What am I doing wrong.
We're working with Office97 on a citrix platform.

Many Thanks
Do While myFile <> ""

'Open document
Set myBook = Workbooks.Open(pathToUse & myFile)
Workbooks.OpenText Filename:=pathToUse & myFile, Origin:=xlMSDOS _
, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
Array( _
6, 1), Array(37, 1), Array(47, 1))


'Document Omzetten
Omzetten


'Nieuwe naam geven (XLS)
Length = Len(myFile)
NwLength = Length - 4
NwFile = Left(myFile, NwLength)


'Print en Sluit het gewijzigde document na de wijzigingen
ActiveWorkbook.PrintOut Copies:=2
ActiveWorkbook.SaveAs Filename:=(PathToSave & NwFile & ".xls"),
FileFormat:=xlWorkbookNormal
ActiveWorkbook.Close


'Volgende document in de map
myFile = Dir$()
Loop

Exit Sub

Errorhandler:
MsgBox ("er is een fout opgetreden, het programma kan niet worden
voortgezet. Bel 212 of ga naar huis!")

End Sub

 

[Bob Phillips]

Can you explain in more detail what the differences between the two runs
are?

 

[Rinze Smit]

Hi Bob,
I can try:
It concerns column D, which contains numbers.
When running step by step, the numbers are converted correctly to 0,5 and
1,63333 (first two rows)
When running by toolbarbutton, the numbers are converted to ,5 and
16333333,00.
So it looks like something changes with de decimal sign (comma or point),
but I can't figure out why.

hope this gives you more information.

Rinze,
Revalidatie Friesland.

 

[keepITcool]

probably got to do with activeworkbook.
when you are in debugging mode it could be
that "thisworkbook" in which the code runs
is the activeworkbook.
(maybe Omzetten reactivate another workbook..

unlike the open function (which returns a workbook object)
OpenText is a method with no return value.
so assign the variable from activeworkbook just after opening the file.

and pass the workbook variable as an argument to the omzetten procedure.
(and inside the omzetten, change the activeworkbook variables to mybook

Sub x()
Dim myFile$, nwFile$, pathToUse$, pathToSave$
Dim myBook As Workbook

myFile = Dir$(pathToUse & myFile)
Do While myFile <> ""
'Open document
Call Workbooks.OpenText(Filename:=pathToUse & myFile, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(37, 1), _
Array(47, 1)))
Set myBook = ActiveWorkbook

'Document Omzetten
Call omzetten(myBook)

'Nieuwe naam geven (XLS)
nwFile = Left(myFile, Len(myFile) - 4)

'Print en Sluit het gewijzigde document na de wijzigingen
With myBook
.PrintOut Copies:=2
.SaveAs Filename:=(pathToSave & nwFile & ".xls"), _
FileFormat:=xlWorkbookNormal
.Close 0
End With
Set myBook = Nothing
'Volgende document in de map
myFile = Dir$()
Loop

Exit Sub

Errorhandler:
MsgBox ( _
"er is een fout opgetreden," & _
" het programma kan niet worden voortgezet. " & vbLf & _
"Bel 212 of ga naar huis!")

End Sub

Sub omzetten(wkb As Workbook)
msgbox wkb.name
End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Rinze Smit wrote :

 

[keepITcool]

and that part of the code is missing.
would be nice had you mentioned it in your first post.

it depends on how the text files are SAVED.
are the SAVED copies "localized" or in USEnglish format?

Following is possible only in excel XP and excel 2003:
for localized files add Local:=true parameter to the Opentext call.
for USenglish files add Local:=false.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Rinze Smit wrote :

 

[Rinze Smit]

I'm trying to keep cool but...
Well sorry, it still doesn't work out. In both my code as your code the
problem is the same.
I can see it if I put a break on the line "Set myBook = ActiveWorkbook".
When I execute step by step, the numbers are shown correct, When I execute
bij toolbarbutton, the numbers are shown incorrect.
I think the import does not behave the same. So it's the 'Call Workbooks'
part that goes wrong. The code after that (in procedure 'omzetten') does not
matter in this.

Hope to figure this out.

Rinze Smit
Revalidatie Friesland

 

[Rinze Smit]

hello,
The import part is not missing. It's:

Set myBook = Workbooks.Open(pathToUse & myFile)
Workbooks.OpenText Filename:=pathToUse & myFile, Origin:=xlMSDOS _
, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
Array( _
6, 1), Array(37, 1), Array(47, 1))

The Workbooks.Open.... is recorded with the macrorecorder. It opens the
textfile and converts it to an Excelfile.

I don't really understand what you mean by 'localised'. I only want to open
a textfile and I don't understand why my macro works OK when using it step
by step or with the 'play-button' and NOT OK when I start the macro with the
toolbarbutton.

Thanks for your imput.

Rinze Smit
Revalidatie Friesland.

 

[keepITcool]

your code opens the file
then reopens the same file via opentext method.

the first line can be removed.

re "localized"
open one of the txt files with notepad.

If numbers are stored with . as decimal separator
and dates appear in mmddyy then USenglish format is used.
open with
Opentext(..... Local:=false)

If numbers are stored with , as decimal and ddmmyy dates
then use
Opentext(....... Local:=True)

Local parameter is ONLY valid if you have Excel XP or newer!!
will generate error on older versions.

Else mail me 1 of the txt files and the workbook with the code.
and I'll have a look.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Rinze Smit wrote :

 

[Rinze]

Hi KeepITcool,

I've been out for a while, but back working on this problem now.
We're trying to esteblish it working on Excel97, so I can't use the
'Local' parameter.
I do understand your statement about me opening the document twice.
I've deleted that first line.
However, it still seems very strange to me that there is a difference
in the data of the imported file when it's imported (macro executed) by
toolbarclik or by 'play-button' in the VBA-editor.
Can you (or anyone else) explain to me why this happens?

Rinze Smit
Revalidatie Friesland.

 

[keepITcool]

why? why is the grass green?

s/b simple to solve by creating a button calls a small proc to save the
file.




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Rinze wrote :

 

[Rinze Smit]

Well,
I'll be stupid, but I don't really understand what you mean in the context
of my problem.

Rinze Smit
Revalidatie Friesland.