Hi guys,
I'm fairly new to VBA so excuse any n00b mistakes.
I'm having trouble with a particular script at the moment and can't seem to get it to work. I'm not even getting a meaningful message or the chance to debug. Through a process of commenting out on the script I have located the line where it's failing.
In a nutshell the script I've written opens a log file from a stated location as a temp workbook. I want to select column A to the last value in column A, copy it and past in a desired range on the main workbook.
I can't seem to get it to copy and paste. I just get a '400' error message.
Here's the code :
Sub ImportTextFile()
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
> </o>
Dim SheetName As String
Dim TMPWorkBook As Workbook
Dim FilePath As String
Dim TxtFilePath As String
Dim TxtFileName As String
Dim myLastRow As Long
Dim myLastColumn As Long
<o> </o>
Application.ScreenUpdating = False
<o> </o>
Set WB = ThisWorkbook
SheetName = "Sheet1"
TxtFileName = "\\ServerName\Directory\Directory\Directory\FileName.log"
Workbooks.OpenText Filename:="\\ServerName\Directory\Directory\Directory\FileName.log"
<o> </o>
Set TMPWorkBook = ActiveWorkbook
TMPWorkBook.Sheets("SheetName").Select
*** Failing Here ****
'Range(Selection, Selection.End(xlDown)).Select
'Cells.Select
'Selection.Copy
'TMPWorkBook.Close savechanges:=False
<o> </o>
Set WB = ActiveWorkbook
'ThisWorkbook.Sheets("Sheet1").Select
'Range("E1").Select
'ActiveSheet.Paste
'Application.CutCopyMode = False
'Cells.Select
'Cells.EntireColumn.AutoFit
'ActiveSheet.Range("E1").Select
<o> </o>
Application.ScreenUpdating = True
<o> </o>
End Sub
What am I doing wrong? Any help greatly received.
Cheers
Nick
I'm fairly new to VBA so excuse any n00b mistakes.
I'm having trouble with a particular script at the moment and can't seem to get it to work. I'm not even getting a meaningful message or the chance to debug. Through a process of commenting out on the script I have located the line where it's failing.
In a nutshell the script I've written opens a log file from a stated location as a temp workbook. I want to select column A to the last value in column A, copy it and past in a desired range on the main workbook.
I can't seem to get it to copy and paste. I just get a '400' error message.
Here's the code :
Sub ImportTextFile()
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o> </o>Dim SheetName As String
Dim TMPWorkBook As Workbook
Dim FilePath As String
Dim TxtFilePath As String
Dim TxtFileName As String
Dim myLastRow As Long
Dim myLastColumn As Long
<o
> </o>Application.ScreenUpdating = False
<o
> </o>Set WB = ThisWorkbook
SheetName = "Sheet1"
TxtFileName = "\\ServerName\Directory\Directory\Directory\FileName.log"
Workbooks.OpenText Filename:="\\ServerName\Directory\Directory\Directory\FileName.log"
<o
> </o>Set TMPWorkBook = ActiveWorkbook
TMPWorkBook.Sheets("SheetName").Select
*** Failing Here ****
'Range(Selection, Selection.End(xlDown)).Select
'Cells.Select
'Selection.Copy
'TMPWorkBook.Close savechanges:=False
<o
> </o>Set WB = ActiveWorkbook
'ThisWorkbook.Sheets("Sheet1").Select
'Range("E1").Select
'ActiveSheet.Paste
'Application.CutCopyMode = False
'Cells.Select
'Cells.EntireColumn.AutoFit
'ActiveSheet.Range("E1").Select
<o
> </o>Application.ScreenUpdating = True
<o
> </o>End Sub
What am I doing wrong? Any help greatly received.
Cheers
Nick
