PDA

View Full Version : Run-time error 438: Object doesn't support this property or method (VBA), Office 2010



Mr0wyx
02-08-2018, 01:21 PM
Hello,


I know this kind a question came up million times but I cant find mistake. This code works on all machines except one. And I tried different ways to solve by checking permissions in trust center settings and so on but no success. So I think maybe error is in code? Can please someone give advice?




Sub TransasImport_Click()


Dim fileDialog As fileDialog
Dim strPathFile As String
Dim dialogTitle As String
Dim wbSource As Workbook
Dim rngToCopy As Range
Dim rngRow As Range
Dim rngDestin As Range
' Dim lngRowsCopied As Long

Application.ScreenUpdating = False

'Message to select file

MsgBox "Select Transas '.xls' export file."

'Import data from XLS file


dialogTitle = "Navigate to and select required XLS file."
Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)

'Call file dialog


With fileDialog
.InitialFileName = "C:\Users\User\Desktop"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "XLS file", "*.xls"
.Title = dialogTitle


If .Show = False Then
MsgBox "File not selected."
Exit Sub
End If
strPathFile = .SelectedItems(1)
End With

Set wbSource = Workbooks.Open(Filename:=strPathFile)

'Clear data in all cells

ThisWorkbook.Sheets("Transas data").Range("A3:L702").Clear
ThisWorkbook.Sheets("Furuno data").Range("B3:R702").Clear
ThisWorkbook.Sheets("Chartworld data").Range("B3:T702").Clear

'Import data code

With wbSource.Worksheets("WAYPOINTS")
Set rngToCopy = .Range(.Cells(2, "A"), .UsedRange.SpecialCells(xlCellTypeLastCell))
For Each rngRow In rngToCopy.Rows
If WorksheetFunction.CountA(rngRow) = 0 Then
rngRow.EntireRow.Hidden = True 'Hides rows with no data
End If
Next rngRow
Set rngDestin = ThisWorkbook.Sheets("Transas data").Cells(3, "A")
rngToCopy.SpecialCells(xlCellTypeVisible).Copy Destination:=rngDestin
' lngRowsCopied = rngToCopy.Columns(1).SpecialCells(xlCellTypeVisible).Count
' MsgBox lngRowsCopied & " rows copied."
.Rows.Hidden = False 'Unhides previously hidden rows
End With

wbSource.Close SaveChanges:=False

'Clear data in all text boxes


ThisWorkbook.Sheets("Export Data").TextBox1.Text = "-"
ThisWorkbook.Sheets("Export Data").TextBox2.Text = "-"
ThisWorkbook.Sheets("Export Data").TextBox3.Text = "-"
ThisWorkbook.Sheets("Export Data").TextBox4.Text = "-"

'Message data imported

MsgBox "The data was imported.."

End Sub

SamT
02-08-2018, 02:10 PM
The location of the error is obvious. Not.

Tell us all about the computer this does not work on. Tell us about those it does work on.

Form the very little info given, I bet the code is good and you have a glitched computer.

Mr0wyx
02-08-2018, 02:49 PM
The location of the error is obvious. Not.

Tell us all about the computer this does not work on. Tell us about those it does work on.

Form the very little info given, I bet the code is good and you have a glitched computer.


Thank you for swift reply. I am happy to hear that the code is ok. The thing is that this code is being tested on corporate computers running same Windows XP, Office 2010 (14.0.7116.5000 32bit) and software is more or less same too. I don't have direct access to that computer but so far communicating with the user we went checking Trust Center settings. This is first idea I came up with. I dont know, if updates could be the case? Or maybe there could be some setting that could couse the problem?

SamT
02-08-2018, 05:47 PM
Could be Updates.

On the bad computer, try compiling the code. You will need to edit the code in order to re-compile it. add

Sub T(): End Sub
Delete it to recompile again. Repeat as needed

Inside the VBA Editor try stepping thru the code using F8.

Try running the code inside the VBA Editor using F5

Get back to us.

Mr0wyx
02-09-2018, 08:06 AM
Could be Updates.

On the bad computer, try compiling the code. You will need to edit the code in order to re-compile it. add

Sub T(): End Sub
Delete it to recompile again. Repeat as needed

Inside the VBA Editor try stepping thru the code using F8.

Try running the code inside the VBA Editor using F5

Get back to us.

Thx! Will try what I can and will revert back..

snb
02-09-2018, 08:44 AM
Why don't you reveal the line where the error occurs ?

Why do you use such redundant code ?


Sub M_snb()
With Application.fileDialog(3)
.Title = "Navigate to and select the required XLS file."
.InitialFileName = "C:\Users\User\Desktop\*,xls*"
if .Show then thisworkbook.sheets.add ,thisworkbook.sheets(thisworkbook.sheets.count),,.selecteditems(1)
end with

with ThisWorkbook
.Sheets("Transas data").Range("A3:L702").Clear
.Sheets("Furuno data").Range("B3:R702").Clear
.Sheets("Chartworld data").Range("B3:T702").Clear
.sheets("WAYPOINTS").usedrange.offset(1).copy .Sheets("Transas data").cells(3,1)
.Sheets("Transas data").columns(1).specialcells(4).entirerow.delete
for j=1 to 4
.sheets("export Data").oleobjects("Textbox" & j).object.Text=""
next
end with

End Sub