1

I have an import code that works perfectly on my 2013 64bit excel; however, I have users that are using 2010 Excel with 32bit and the code does not work. I would be very grateful if someone would look at the below code and tell me what I need to change in order for my 2010 32bit users to be able to use it:

Sub TransferData()
''<<<<unprotect code
Dim i As Integer
For i = 1 To Sheets.Count
Sheets(i).Unprotect password:="ADMIN"
Next i
'
''<<<<<main code
'
Application.ScreenUpdating = False
Sheets("Dashboard").Activate
Sheets("DASF Data").Range("DASF_Range").ClearContents
'
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = ActiveWorkbook

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.xls (*.xls),")

If FileToOpen = False Then
MsgBox "No File Specified.", vbExclamation, "ERROR"
Exit Sub
Else
Set wb2 = Workbooks.Open(Filename:=FileToOpen)

For Each Sheet In wb2.Sheets
    If Sheet.Visible = True Then
      Range("A2:Y10000").Select
Selection.Copy
Windows("DASF MANAGEMENT TOOL (CONUS Ver 1.0).XLSB").Activate
Sheets("DASF Data").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Sheets("Dashboard").Activate

End If
Next Sheet
End If
Workbooks("DASF.XLS").Close SaveChanges:=False

End Sub
Community
  • 1
  • 1
Michael Molina
  • 11
  • 2
  • 2
    where/ what is the error?... I can't see anything that wouldn't work on 2010 32bit there – CallumDA Mar 28 '17 at 20:25
  • Try protecting the sheet on the older version of Excel instead – Slai Mar 28 '17 at 20:35
  • “Run-time error ‘400036’” Application-defined or object-defined error. – Michael Molina Mar 28 '17 at 20:36
  • On which line do you get the error? – YowE3K Mar 28 '17 at 20:36
  • I have a button to import the data which is tied to the VBA in this post. When the button is selected the users are received the error “Run-time error ‘400036’” Application-defined or object-defined error. – Michael Molina Mar 28 '17 at 20:38
  • @MichaelMolina, can you click debug? It will highlight the line that spawns the error -- that's the info we need – CallumDA Mar 28 '17 at 20:41
  • The only two things I can suggest are: (1) change `Dim i As Integer` to `Dim i As Long`. I doubt that there is any file where this would have an impact. But that's the only difference here between 32-bit and 64-bit (where any Integer is automatically converted / stored as Long). (2) change `Sheets("DASF Data").Select` to `Sheets("DASF Data").Activate`. Note that only cells and ranges can get selected. Sheets must be activated. In earlier versions of Excel `select` still worked in combination with sheets. In earlier versions this is often creating an error. But again this doesn't seem to relate – Ralph Mar 28 '17 at 20:46
  • No it doesn't provide the option to select debug...only the end button is available on the error. – Michael Molina Mar 28 '17 at 20:46
  • does the open file dialog show? do you select a file and then get an error? is the error immediate? -- what more can you tell us? – CallumDA Mar 28 '17 at 20:49
  • Thank you all, Ralph- I will try out your suggestions and have the user retry.. – Michael Molina Mar 28 '17 at 20:51
  • For your consideration: http://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long – Ralph Mar 28 '17 at 21:28
  • Perhaps its due to [code incompatibility between 32/64 bits of Excel](https://msdn.microsoft.com/en-us/library/office/mt662476.aspx)? Everyone has this `DASF MANAGEMENT TOOL (CONUS Ver 1.0).XLSB` on their computer loaded without error? – PatricK Mar 29 '17 at 00:32
  • Could it be that the error isn't in the code but in the button? Try running the code using F5 from the VBE window. Look at the button's OnAction command line. Make sure that the macro is installed where the document can find it. Try running it from the Developer tab -> Macros > Run button. – Variatus Mar 29 '17 at 00:46
  • Excel 2013 uses more advanced protection, so I expect 2010 to have some problems with unprotecting it http://www.spreadsheet1.com/sheet-protection-2013.html – Slai Mar 29 '17 at 09:29

1 Answers1

1

That error number does not necessarily have anything specifically to do with your listed code. It could be an error anywhere in your modules: or it could be corrupt code in the compiled version of one of your modules. It could even be a broken library reference.

To exclude the first possibility, do a manual "compile all' of your code.

To fix the second possibility, create a new spreadsheet, and copy the text from your old spreadsheet into new modules in your new spreadsheet. Or download and use an Excel 'decompiler'.

david
  • 2,435
  • 1
  • 21
  • 33