Sleeper: Macro built on Win64 not running on Win32

[rvhoeft]

Hi,

I've built a macro that opens a text file, rearranges some columns then copies the columns into the spreadsheet that contains the macro. Problem is that it runs perfectly on Win64 machines, but will exit consistently, but improperly when running on a win32 machine. I'm guessing it may have something to do with the application.filedialog method - guessing because I'm not a developer by trade or education.

What do I need to do to ensure the macro will run flawlessly on the win32 machine? I've read a little about compiler constants, but not sure if that is the magic fix.

Any pointers to existing threads or pieces of code to fix the problem would be greatly appreciated.

Thanks.

[Aflatoon]

I can't think of anything specific to 64bit vs 32 bit Windows that would cause that kind of issue. (There are plenty of incompatibilities with 32bit vs 64bit Office, but they tend to be the other way round - i.e. break in 64 bit Office.)

Are you running the same Office versions on each machine? Can you post the actual code?

[rvhoeft]

Here's the VBA code:


Sub Macro6()
'Keyboard Shortcut: Ctrl+Shift+Q
Dim LastRow As Long ' Last row number of text file to import
Dim strpick As String '
Dim CurrentName As String ' Name of text file to import
Dim CurrentSheet As String ' Name of text file worksheet
Dim RTGFileName As String ' Name of Project workbook
Dim newCol As Long
Dim IndentResponse As Integer ' Used in Indent prompt message box
Dim Myfile As FileDialog ' For dialog box to select file to import
RTGFileName = Application.ActiveWorkbook.Name
MyTitle = "File Import"
MyMsg = "Select file to import"
response = MsgBox(prompt:=MyMsg, Buttons:=vbOKOnly, Title:=MyTitle)
Set Myfile = Application.FileDialog(msoFileDialogOpen)
On Error GoTo ErrorOut
Myfile.AllowMultiSelect = False ' allow only one file to be chosen
Myfile.Title = "Select text file to import"
Myfile.Filters.Clear
Myfile.Filters.Add "Text Files", "*.csv, *.txt, *.prn", 1
Myfile.Filters.Add "ALL Files", "*.*"
' Myfile.Filters.Count 1
Myfile.FilterIndex = 1
Myfile.Show
strpick = Myfile.SelectedItems(1)
' Open and format text file for import
Workbooks.OpenText Filename:=strpick _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
Array(22, 1)), TrailingMinusNumbers:=True
Windows(msoFileDialogOpen).Activate ' Activate text file to rearrange and copy columns
LastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
CurrentName = ActiveSheet.Name
Columns("C:C").Select ' Revision Column
Selection.Cut
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Columns("E:E").Select ' Phantom Column
Selection.Cut
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Columns("S:S").Select ' Fixed Lead Time Column
Selection.Cut
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Columns("M:M").Select ' Description Column
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("K:K").Select ' Analyst Code Column
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("A2:c" & LastRow).Select ' Copy Level, Analyst Code & Part Number
Selection.Copy
Windows(RTGFileName).Activate ' Back to project file
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("k9").Select
Windows(CurrentName).Activate ' Back to text file
Range("d2:i" & LastRow).Select ' Copy Description, Qty, UoM, Make/Buy, Phantom, & Fixed Lead Time
Application.CutCopyMode = False
Selection.Copy
Windows(RTGFileName).Activate ' Back to project file
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
LastRow = LastRow + 7 ' Sets last row + header rows
Range("Af9:Ag9").Select ' Copies formulas in AF9 and AG9 down to last row
Application.CutCopyMode = False
Selection.Copy
Range("Af10:Af" & LastRow).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("Ab9").Select
Windows(CurrentName).Activate ' Close text file and back to project file
ActiveWorkbook.Close savechanges:=False
Cells(9, 2) = 0 ' Create Level 0 for BOM indention
GoTo ConvertComplete
ConvertComplete:
CurrentSheet = ActiveSheet.Name ' Get current sheet name in project file
IndentResponse = MsgBox(prompt:="Indent BOM?", Title:="BOM Indention", Buttons:=vbYesNoCancel)
Select Case IndentResponse
Case Is = vbYes ' Indent BOM and Fixed Lead Times
Range("d9").Select ' Set cursor at location of final assy part number
For irows = 9 To LastRow ' Rows 1 through 8 are worksheet header (counter starts at 9)
iParts = 4 ' Column 4 (D) contains BOM list
iFixedLT = 16 ' Column 16 (P) contains Fixed Lead Times
Cells(irows, iParts).Select
Selection.Cut ' Cut part number from current location
CellValue = Worksheets(CurrentSheet).Cells(irows, iParts - 2).Value ' get BOM level for indention
newCol = iParts + CellValue
Cells(irows, newCol).Select ' Move cursor over columns based on level number
ActiveSheet.Paste ' Paste part number
Cells(irows, iFixedLT).Select
Selection.Copy
CellValue = Worksheets(CurrentSheet).Cells(irows, iParts - 2).Value
newCol = iFixedLT + CellValue + 1 ' +1 for BOM level 0
Cells(irows, newCol).Select
ActiveSheet.Paste ' Paste Fixed Lead Time
Next irows
Range("AB9").Select
MsgBox "Conversion Complete"
Case Is = vbNo ' Leave BOM as is
MsgBox "No Indention. Conversion Complete"
Case Is = vbCancel ' Same as vbNo
MsgBox "User Exited. Conversion Complete"
End Select
GoTo ExitSub
ErrorOut:
MsgBox "User Cancelled" ' Cancel out from select file to import & exit macro
ExitSub:
End Sub

The reason I think (guess) that the problem lies with the application.filedialog method is that the macro will run the 1st copy/paste operation, move the cursor back to the cell location for the next paste operation, then goto ErrorOut and exit the macro.

I originally used a "With" statement for the application.filedialog, but moving away from that didn't solve the problem.

[Aflatoon]

Try commenting out the On Error GoTo ErrorOut line to find out the true error.

Also, this line:

Windows(msoFileDialogOpen).Activate

just means

Windows(1).Activate
I'm not really sure why you use the msoFileDialogOpen constant there?

[rvhoeft]

The entire macro is a patchwork of recorded pieces as well as picked up from the web. THe msoFileDialogOpen was copied from the web somewhere adapted to my needs and it worked. If there is a more efficient and effective way of executing the method, please let me know with some examples. As I said, I'm guessing on a lot of the macro content.

Thanks again.

[Aflatoon]

I gave you the equivalent in my last post. Did you try commenting out the error handler line?

[rvhoeft]

Sorry. Added reply then ran off before posting.