Hello to everybody,
I am trying to develop a little application with Excel, even if I am a
novice with Vba and every time that I launch Excel 2007/Vista stop to work.
Basically I have an external Excel file and I want to import some values
into my app.:
- import the last row (A:G range)
- import the penultima row (A:G range)
- import the last 14th rows (A:G range)
Below the code I am trying.
Any help also to improve the routine is really appreciated.
Thanks in advance and Regards
John


Public Sub GenericoLast()
Dim App As New Excel.Application, SourceFile As Object
Dim SourceRange1 As Range, TargetRange1 As Range
Dim SourceRange2 As Range, TargetRange2 As Range
Dim SourceRange3 As Range, TargetRange3 As Range
Dim ExternalFileName As String, ExternalSheetName As String

'check if file exist
If Not FileExists("C:\Users\PC\Documents\generico.xls") Then
MsgBox "File not found", vbExclamation, "Attention..."
GoTo RigaErrore
Else

' Definitions
' -----------------------------------------
ExternalFileName = "C:\Users\PC\Documenti\generico.xls"
ExternalSheetName = "generico"
Set TargetRange1 = [Daily!A7:E7] ' Penultimate values
Set TargetRange2 = [Daily!A8:E8] ' last value
Set TargetRange3 = [Daily!B20:H33] ' TC2
' -----------------------------------------
Set SourceFile = App.Workbooks.Open(ExternalFileName)

'Import penultimate value
Set SourceRange1 = SourceFile.Worksheets(ExternalSheetName).Range("A1 ")
If Not IsEmpty(SourceRange1(2, 1)) Then
Set SourceRange1 = SourceRange1.Resize _
(SourceRange1.End(xlDown).Row - SourceRange1.Row + 1, 1)
End If
Set SourceRange1 = SourceRange1(SourceRange1.Rows.Count - 1).Resize(1,
6)
TargetRange1 = SourceRange1.Value

'Import last value
Set SourceRange2 = SourceFile.Worksheets(ExternalSheetName).Range("A1 ")
If Not IsEmpty(SourceRange2(2, 1)) Then
Set SourceRange2 = SourceRange2.Resize _
(SourceRange2.End(xlDown).Row - SourceRange2.Row + 1, 1)
End If
Set SourceRange2 = SourceRange2(SourceRange2.Rows.Count - 0).Resize(1,
6)
TargetRange2 = SourceRange2.Value

'Import TC2 values
Set SourceRange3 = SourceFile.Worksheets(ExternalSheetName).Range("A1 ")
If Not IsEmpty(SourceRange3(2, 1)) Then
Set SourceRange3 = SourceRange3.Resize _
(SourceRange3.End(xlDown).Row - SourceRange3.Row + 1, 1)
End If
Set SourceRange3 = SourceRange3(SourceRange3.Rows.Count - 13).Resize(14,
7)
TargetRange3 = SourceRange3.Value

SourceFile.Close
App.Quit

End If
Kill "C:\Users\PC\Documents\generico.xls"

RigaErro
Exit Sub
End Sub