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