Copying
 

I would like:

Open a workbook with a macro and check if the sheets "list of houses"
and "database" are in that file. And, if yes, the sheets must be
copied to myfile. In both cases the opened file must be closed (after
checking / copying).

But, If the sheets are not available, the file doesn't close and I
don't understand why.

Any help would be appriciated.

------------

Sub importerendb()
Dim bk As Workbook
Range("rekenvel!E2").Value = Range("rekenvel!b13").Value & "\" &
importeren.databasescombo.Value & ".xls"
Dim bestandsnaam
bestandsnaam = Range("rekenvel!E2").Value
Workbooks.Open Filename:=bestandsnaam, Password:="abc"
For Each n In ActiveWorkbook.Names
n.Delete
Next
Dim sh As Worksheet
On Error Resume Next
Set sh = ActiveWorkbook.Worksheets("list of houses")
If Err < 0 Then
bk.Close SaveChanges:=False
MsgBox "No list of houses found"
Else
Set sh = ActiveWorkbook.Worksheets("database")
If Err < 0 Then
bk.Close SaveChanges:=False
MsgBox "No database found"
Else

Sheets(Array("lijstkerken", "database")).Move Befo=Workbooks( _
"myfile.xls").Sheets(1)
bk.Close SaveChanges:=False
End If
End If
Unload importeren
End Sub

Copying
 

The bk Workbook object variable is never set to the opened workbook, so
when the bk.Close method is executed it throws an error, but the error
is ignored due to the On Error Resume Next


In article . com,
wrote:

I would like:

Open a workbook with a macro and check if the sheets "list of houses"
and "database" are in that file. And, if yes, the sheets must be
copied to myfile. In both cases the opened file must be closed (after
checking / copying).

But, If the sheets are not available, the file doesn't close and I
don't understand why.

Any help would be appriciated.

------------

Sub importerendb()
Dim bk As Workbook
Range("rekenvel!E2").Value = Range("rekenvel!b13").Value & "\" &
importeren.databasescombo.Value & ".xls"
Dim bestandsnaam
bestandsnaam = Range("rekenvel!E2").Value
Workbooks.Open Filename:=bestandsnaam, Password:="abc"
For Each n In ActiveWorkbook.Names
n.Delete
Next
Dim sh As Worksheet
On Error Resume Next
Set sh = ActiveWorkbook.Worksheets("list of houses")
If Err < 0 Then
bk.Close SaveChanges:=False
MsgBox "No list of houses found"
Else
Set sh = ActiveWorkbook.Worksheets("database")
If Err < 0 Then
bk.Close SaveChanges:=False
MsgBox "No database found"
Else

Sheets(Array("lijstkerken", "database")).Move Befo=Workbooks( _
"myfile.xls").Sheets(1)
bk.Close SaveChanges:=False
End If
End If
Unload importeren
End Sub

Copying
 

you're right....

but what would be a good way to check if the mentioned sheets are in
the opened file. And if not, give an error and close the file?

Copying
 

The way you're doing it isn't wrong - you just need to set the variable.
I might reorganize it like this:

Dim bk As Workbook
Dim wsHouses As Worksheet
Dim wsDatabase As Worksheet
Dim wsMoveBefore As Worksheet
Dim bestandsnaam As String
With ActiveWorkbook.Worksheets("rekenvel")
bestandsnaam = .Range("B13").Text & _
Application.PathSeparator & _
importeren.databasescombo.Value & ".xls"
.Range("E2").Value = bestandsnaam
End With
Set bk = Workbooks.Open(Filename:=bestandsnaam)
On Error Resume Next
Set wsHouses = bk.Worksheets("lijstkerken")
Set wsDatabase = bk.Worksheets("database")
On Error GoTo 0
If Not wsHouses Is Nothing Then
If Not wsDatabase Is Nothing Then
With Workbooks("myfile.xls")
wsDatabase.Move Befo=.Sheets(1)
wsHouses.Move Befo=.Sheets(1)
End With
Else
MsgBox "No database found"
End If
Else
MsgBox "No list of houses found"
End If
bk.Close SaveChanges:=False

But that's more a matter of taste than anything else.


In article .com,
wrote:

you're right....

but what would be a good way to check if the mentioned sheets are in
the opened file. And if not, give an error and close the file?

Copying
 

Thanks a lot, I changed some small things (for my situation) and it
works perfect!. Thanks!

Copying
 

Change these four lines:

bk.Close SaveChanges:=False
End If
End If
Unload importeren
End Sub

To:

End If
End If
bk.Close SaveChanges:=False
Unload importeren
End Sub



" wrote:

I would like:

Open a workbook with a macro and check if the sheets "list of houses"
and "database" are in that file. And, if yes, the sheets must be
copied to myfile. In both cases the opened file must be closed (after
checking / copying).

But, If the sheets are not available, the file doesn't close and I
don't understand why.

Any help would be appriciated.

------------

Sub importerendb()
Dim bk As Workbook
Range("rekenvel!E2").Value = Range("rekenvel!b13").Value & "\" &
importeren.databasescombo.Value & ".xls"
Dim bestandsnaam
bestandsnaam = Range("rekenvel!E2").Value
Workbooks.Open Filename:=bestandsnaam, Password:="abc"
For Each n In ActiveWorkbook.Names
n.Delete
Next
Dim sh As Worksheet
On Error Resume Next
Set sh = ActiveWorkbook.Worksheets("list of houses")
If Err < 0 Then
bk.Close SaveChanges:=False
MsgBox "No list of houses found"
Else
Set sh = ActiveWorkbook.Worksheets("database")
If Err < 0 Then
bk.Close SaveChanges:=False
MsgBox "No database found"
Else

Sheets(Array("lijstkerken", "database")).Move Befo=Workbooks( _
"myfile.xls").Sheets(1)
bk.Close SaveChanges:=False
End If
End If
Unload importeren
End Sub