Copy range from Temp WorkBook to Main WorkBook

[Barquois]

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-comfficeffice" /><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

 

[CharlesChuckieCharles]

Try

Sub ImportTextFile()
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
 
[COLOR=red]Dim wb As Workbook[/COLOR]
 
Application.ScreenUpdating = False
Set wb = ThisWorkbook
SheetName = "Sheet1"
TxtFileName = "[URL="file://\\ServerName\Directory\Directory\Directory\FileName.log"]\\ServerName\Directory\Directory\Directory\FileName.log[/URL]"
Workbooks.OpenText Filename:="[URL="file://\\ServerName\Directory\Directory\Directory\FileName.log"]\\ServerName\Directory\Directory\Directory\FileName.log[/URL]"
Set TMPWorkBook = ActiveWorkbook
TMPWorkBook.Sheets(SheetName).Select
*** Failing Here ****
[COLOR=red]Selection.Copy[/COLOR]
'Set WB = ActiveWorkbook
wb.Sheets(SheetName).Select
Range("E1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
TMPWorkBook.Close savechanges:=False
 
Cells.Select
Cells.EntireColumn.AutoFit
ActiveSheet.Range("E1").Select
Application.ScreenUpdating = True
End Sub

when posting code use "[" Code "]" at the start and "[/" Code "]" to finish

 

[Barquois]

Thanks Charles,

Sadly still getting the 400 error.

Regards

Nick

 

[CharlesChuckieCharles]

Try

Sub ImportTextFile()
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

Dim wb As Workbook
 
Application.ScreenUpdating = False
Set wb = ThisWorkbook
SheetName = "Sheet1"
Workbooks.OpenText Filename:="[URL="file://servername/Directory/Directory/Directory/FileName.log"]\\ServerName\Directory\Directory\Directory\FileName.log[/URL]"

Set TMPWorkBook = ActiveWorkbook
TMPWorkBook.Sheets(1).Range("$A$1:" & Cells.SpecialCells(xlCellTypeLastCell).Address).Select
Selection.Copy
 
wb.Sheets(SheetName).Activate

Range("E1").Select

ActiveSheet.Paste

Application.CutCopyMode = False

TMPWorkBook.Close savechanges:=False
 
Cells.Select
Cells.EntireColumn.AutoFit
ActiveSheet.Range("E1").Select
Application.ScreenUpdating = True
End Sub

This works for me

 

[Barquois]

Thanks Charles,

This does work but I want it to copy down columns A and B to the last populated cell.

How do I code that?

Regards

Nick

 

[CharlesChuckieCharles]

Thanks Charles,

This does work but I want it to copy down columns A and B to the last populated cell.

How do I code that?

Regards

Nick

This does work but I want it to copy down columns A and B to the last populated cell.

which file /side/ workbook

 

[Barquois]

This does work but I want it to copy down columns A and B to the last populated cell.

which file /side/ workbook

It's okay, scratch that.

It was only copying across 4 or 5 cells for some reason but now it's copying the whole sheet, which is fine as I can manipulate the other side.

Many Thanks for your help!!

Regards

Nick

 

[Barquois]

Okay, so I've now got the data I want from the log file which contains a start date and time of a process.

I'm basically trying to calculate the time difference between the start of the process and the current data and time.

To do this, I'm simply making cell C10 equal the first date and time entry from the log and in C11 using the =NOW() function to get the current date and time (by format).

Here's the crux,

The log output holds the date in this format yyyy/mm/dd (2011/07/01), although I've changed the format to dd/mm/yyyy hh:mm:ss. This results in the date coming through as 07/01/2011, which is wrong.

Cell C10 is formatted as dd/mm/yyyy hh:mm:ss, making the date 07/01/2011, same as the first entry in the log file.

Cell C11 with the =NOW() function is formatted like so dd/mm/yyyy hh:mm:ss making the date 06/07/2011, which is correct.

Basically, C11-C10 is resulting in 4322 hours 29 minutes and 5 seconds.

Is there a way that I can make the dates all the same format so the calculations are correct?

I've tried this, but it's still not working.

Columns("E:E").Select
Selection.NumberFormat = "dd/mm/yyyy hh:mm:ss"
Range("C10").Select
Selection.NumberFormat = "dd/mm/yyyy hh:mm:ss"
Range("C11").Select
Selection.NumberFormat = "dd/mm/yyyy hh:mm:ss"

HELP!!

Regards

Nick

 

[CharlesChuckieCharles]

use the DateValue() function so DateValue(Now()) will only return the date part

 

[Barquois]

The problem is I need to work out the length of time in hours, minutes and seconds between two dates and time that are different formats.

Regards

Nick