Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
running macro step by step different from normal run??
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
running macro step by step different from normal run??
Can you explain in more detail what the differences between the two runs
are? -- HTH Bob Phillips "Rinze Smit" wrote in message l.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
running macro step by step different from normal run??
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. "Bob Phillips" wrote in message ... Can you explain in more detail what the differences between the two runs are? -- HTH Bob Phillips "Rinze Smit" wrote in message l.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
running macro step by step different from normal run??
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 : |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
running macro step by step different from normal run??
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 : So it looks like something changes with de decimal sign (comma or point), but I can't figure out why. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
running macro step by step different from normal run??
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 "keepITcool" wrote in message ft.com... 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 : |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
running macro step by step different from normal run??
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" wrote in message ft.com... 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 : So it looks like something changes with de decimal sign (comma or point), but I can't figure out why. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
running macro step by step different from normal run??
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 : 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)) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
running macro step by step different from normal run??
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 wrote: 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 : 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)) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
running macro step by step different from normal run??
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 : 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? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
running macro step by step different from normal run??
Well,
I'll be stupid, but I don't really understand what you mean in the context of my problem. Rinze Smit Revalidatie Friesland. "keepITcool" wrote in message .com... 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 : 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Drop Down List with Step by Step Instructions for 2007 | Excel Worksheet Functions | |||
Need step by step to add invoice numbering to excel template | New Users to Excel | |||
Macro Step by Step | Excel Discussion (Misc queries) | |||
What is the step-by-step procedure for making a data list? | Excel Discussion (Misc queries) | |||
I need step by step instructions to create a macro for 10 imbedde. | Excel Worksheet Functions |