[hlp]: How to copy a sheet to new wkbook and close the var at the end
 

Hi

I'm developing an Excel VBA procedure and I need some support.
I believe I'm doing a very basic error...

WHAT AM I DOING?
I'm opening an excel workbook and link it to a workbook variable type,
then I'm copying a sheet to a new book and *trying* to link it to a new
workbook variable type

At the moment I can do this with the piece of code at the buttom, but I
believe that this is not the best way to do it.

WHY?
Because, this procedure is causing me some troubles at the end.

What happens in the last line of the code is that, besides I don't have
any error, at the end of the procedure the workbook stills open in the
memory and it should be deleted from the memory with the -Set ... =
nothing-.



Code:
--------------------
Set wkInterligBook = Workbooks.Open(sFullPathToInterligFile)

wkInterligBook.Sheets(sIcTID).Copy
wkInterligBook.Close
Set wkInterligBook = Nothing

Set wkInterligBook = ActiveWorkbook
wkInterligBook.SaveAs sFullPathToFinalFile

(...)

wkInterligBook.Save
wkInterligBook.Close
Set wkInterligBook = Nothing
--------------------


Can someone please point me to the right direction.
Many thanks.
P&V


--
pao_e_vinho
------------------------------------------------------------------------
pao_e_vinho's Profile: http://www.excelforum.com/member.php...o&userid=21360
View this thread: http://www.excelforum.com/showthread...hreadid=554625

[hlp]: How to copy a sheet to new wkbook and close the var at the end
 

I'm still stucked with this problem.

No one can help

--
pao_e_vinh
-----------------------------------------------------------------------
pao_e_vinho's Profile: http://www.excelforum.com/member.php...fo&userid=2136
View this thread: http://www.excelforum.com/showthread.php?threadid=55462

[hlp]: How to copy a sheet to new wkbook and close the var at the end
 

Bumping with more details.

I've this piece of test code and I'm having problems because at the end
the -wkInterligBook- wkbook doesn't unload with the -Set wkInterligBook
= Nothing- command.

Can someone help me?


Code:
--------------------
Sub Test()

Dim sFullPathToInterligFile As String
Dim sFullPathToFinalFile As String
Dim wkInterligBook As Workbook

Dim con As ADODB.Connection


sFullPathToInterligFile = ThisWorkbook.Path & "\Test_File.xls"
sFullPathToFinalFile = ThisWorkbook.Path & "\Test_File_Copy.xls"

Set wkInterligBook = Workbooks.Open(sFullPathToInterligFile)

wkInterligBook.Sheets(1).Copy
wkInterligBook.Close
Set wkInterligBook = Nothing

Set wkInterligBook = ActiveWorkbook
wkInterligBook.SaveAs sFullPathToFinalFile

DBEngine.RegisterDatabase "MS Excel Database", _
"Microsoft Excel Driver (*.xls)", True, _
"DBQ=" & sFullPathToFinalFile & ";DESCRIOTION=ODBC para Excel"

Set con = New ADODB.Connection
con.Open "Data Source=MS Excel Database"
con.CursorLocation = adUseClient

' (...)
' (...)
' (...)
' (...)
' (...)

con.Close
Set con = Nothing

wkInterligBook.Save
wkInterligBook.Close
Set wkInterligBook = Nothing
End Sub
--------------------


The references are the following
[image:
http://i22.photobucket.com/albums/b3...eferences.jpg]

and you need to have an (any) excel file name -Test_File.xls- in the
running folder.


--
pao_e_vinho
------------------------------------------------------------------------
pao_e_vinho's Profile: http://www.excelforum.com/member.php...o&userid=21360
View this thread: http://www.excelforum.com/showthread...hreadid=554625