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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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?
 
Upvote 0
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.
 
Upvote 0
Maybe something like this:-
Rich (BB code):
myDest = Sheets("MCS").Cells(Sheets("MCS").Rows.Count, "A").End(xlUp).Offset(1, 0)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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
Back
Top