Run Time Error '438' - Object doesn't support this property or method

Stoyan

New Member
Joined
Apr 22, 2013
Messages
3
Hi,

This error occurs after some changes that i made: Run Time Error '438' - Object doesn't support this property or method.

What i need from this code is- when i click the Button (this MACRO is assigned to it) --> to choose a TXT file, which should be uploaded in worksheet - MSC, which is not the Active Sheet. I think that the problem is coming from this.

Below is my code... I will appreciate if you have any suggestions. Thanks



Sub MCS()
'
' MCS Macro
'
Dim myFile
Dim myDest As String

' Find address of first blank row in column A after last row of data in column A
Sheets("MCS") = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Address

' Browse to file
myFile = Application.GetOpenFilename("All Files,*.*")
If myFile = False Then
Exit Sub
End If

' Open file
'
With Sheets("MCS").QueryTables.Add(Connection:="TEXT;" & myFile, _
Destination:=Range(myDest))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 866
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Sheets("MCS") is a Worksheet object. Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Address returns a string containing a cell address, e.g. $A$1. You can't assign a string to a Worksheet object.

What are you trying to do with this statement exactly?
 

Stoyan

New Member
Joined
Apr 22, 2013
Messages
3
Sheets("MCS") is a Worksheet object. Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Address returns a string containing a cell address, e.g. $A$1. You can't assign a string to a Worksheet object.

What are you trying to do with this statement exactly?


The first version of this line was:

myDest = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Address

It was working, but the TXT file was uploaded in the same Workseet, where the Button is. And I need the uploaded information to be on another - in this case "MCS" sheet.
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Maybe something like this:-
Rich (BB code):
myDest = Sheets("MCS").Cells(Sheets("MCS").Rows.Count, "A").End(xlUp).Offset(1, 0)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Declare myDest as range, set it like this.
Code:
Dim myDest As Range

  With Sheets("MCS")
     Set myDest = .Cells(.Rows.Count,1).End(xlUp).Offset(1)
  End With
Then use it like this.
Code:
With Sheets("MCS").QueryTables.Add(Connection:="TEXT;" & myFile, _
 Destination:=myDest
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,964
Members
413,955
Latest member
FalcoDaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top