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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,792
Office Version
  1. 365
Platform
  1. Windows
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,792
Office Version
  1. 365
Platform
  1. Windows
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,344
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,903
Messages
5,834,312
Members
430,276
Latest member
legalcriminal015

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