How to save and close an excel using Access VB code

[arhm]

I have two procedures in two different access forms. First procedure executes fine. It ends up with creating a Table1 by importing the data successfully from the Excel file. But after that later when I call procedure 2, it throws the below error when it is trying to import the data in to Table2. I think the Excel "Test.xls" did not save properly and close in the Procedure 1. I could see some Excel thread running in the Task Manager. How can I handle this. Please advice. Your expertise help will be truly appreciated. I am absolutely new to this VB-Access code world.


Error: "'MySheet2$'is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long."


Procedure 1:
Set xlApp = CreateObject("Excel.Application")
xlApp.workbooks.Open "C:\test.xls"
xlApp.Worksheets.Add(After:=xlApp.Worksheets(xlApp.Worksheets.Count)).Name = "Mysheet1"
xlApp.Sheets(1).Columns(1:4).Copy xlApp.Sheets("Mysheet1").range(xlApp.Sheets("Mysheet1").Cells(1, 1), xlApp.Sheets("Mysheet1").Cells(1, 1))
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table1", "C:\test.xls", True, "Mysheet1!"
xlApp.workbooks.Close


Procedure 2:
Set xlApp = CreateObject("Excel.Application")
xlApp.workbooks.Open "C:\test.xls"
xlApp.Worksheets.Add(After:=xlApp.Worksheets(xlApp.Worksheets.Count)).Name = "Mysheet2"
xlApp.Sheets(1).Columns(5:10).Copy xlApp.Sheets("Mysheet2").range(xlApp.Sheets("Mysheet2").Cells(1, 6), xlApp.Sheets("Mysheet2").Cells(1, 6))
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table2", "C:\test.xls", True, "Mysheet2!"
xlApp.workbooks.Close

 

[xenou]

Duplicate:
http://www.mrexcel.com/forum/showthread.php?p=1938881&posted=1#post1938881