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

 

[dk]

Hi

You need to quit the application using <em>xlApp.Quit</em> at the end of your code. That should get rid of the process.

DK

 

[arhm]

DK. I appreciate your quick reply...
There problem still persists. The first Excel thread is not killed.. it throws the same error....
Pls do advice and help....

 

[arhm]

what I am observing is when a DoCmd.TransferSpreadsheet command is executed, then the Excel.exe process remains open in running state for ever, even after the complete execution.

How can we tackle this.. any ideas..

 

[xenou]

I'm not thrilled about these lines:

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

Do they really work?

And this one:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table1", "C:\test.xls", True, "Mysheet1!"

That doesn't seem to fit the arguments list for the method.


Do yourself a favor, and create some object references to simplify your code:

Dim xlApp As Object
Dim wb As Object
Dim wsOrig As Object
Dim wsNew As Object

Set xlApp = CreateObject("Excel.Application")

With xlApp

    Set wb = xlApp.Workbooks.Open "C:\test.xls"
    Set ws = wb.Sheets(1)
    wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
    set wsNew = wb.Worksheets(wb.Worksheets.Count)
    wsNew.Name = "MySheet1"
    wsOrig.Columns(1:4).Copy wsNew.Cells(1,1)
    
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table1", "C:\test.xls", True, "Mysheet1!"
    
    wb.Close SaveChanges:=False
    
    xlApp.Quit

End With

And test that final argument for the range. Its odd to see an exclamation point in there, and the help file on transferspreadsheet says to leave this argument blank to import the whole sheet (which presumably you can do, since your copy the four columns you need to a new sheet).

 

[arhm]

The Problem still exists. IF you open a Excel file and then execute DoCmd.TransferSpreadsheet on that excel sheet then you will see that there is Excel.exe running in the task manager which doesnt end.

Now you may ask why I am openning up an excel sheet and running DoCmd.TransferSpreadsheet, this because of the fact that the excel sheet what I get from other group (system) is not being recognized, it says "External table is not in format". When I copy the content of that excel in another excel sheet locally, then it works fine.

As of now folowing this might be helpful to me.
1) If I open a excel sheet and then execute DoCmd.TransferSpreadsheet on it then I shouldnt see Excel.exe running in the task manager. suggest any command to kill that.
2) Or how could I overcome the error "External table is not in format".

Please advice/help

 

[xenou]

Whats in the columns 1-4?

Alex

 

[arhm]

Col1 to 4 is just a text data... See, I just gave it for example.. What I am exactly doing is taking a input excel file, it has 84 columns in its first sheet, I then create and add a worksheet and I move 50 desired columns from Sheet1 to the new sheet, and then I load the new sheet data in to access table.

Everything works fine. The problem is I am getting an input excel file from some third party... when i feed that excel sheet and execute the command DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table1", "C:\test.xls", True, "Mysheet1!"

system throws an error "External table is not in format"

but when I open that Excel file and then execute the DoCmd.TransferSpreadsheet , then data gets loaded with no problem (might be because the excel is open and the speadseet format gets overided) ... but this process is not good as when u open an excel file, u get Excel.exe process created ... without closing this process if u execute the DoCmd.TransferSpreadsheet which reads the same excel file which is held by the process Excel.exe then it gets in to some kind of dead lock... the process doesnt go away even if u close excel sheet or MS Access.. it has to be forcebly killed in the Task Manager....

I am looking for one of the two things
1) Either Resolve "External table is not in format"
2) To kill the Excel.exe dynamically via code

 

[xenou]

This works for me:

DoCmd.TransferSpreadsheet acImport, , "Table1", "C:\test.xls", True

I would avoid creating the excel application.

usually problems of this kind have to do with anomalies in the data. Clean up the spreadsheet, then import it. Also, read help on TransferSpreadsheet so you are sure of all the options.

 

[xenou]

ahrm,
just fyi some guidance on posting threads in more than one place can be found HERE. Also, my last thread is an example of a successful transferspreadsheet and the Excel file was closed so I think there's either a problem with your Excel data itself that corrupts the transfer, or you are not using the correct options for the transfer spreadsheet command. It may be that the Excel data itself is not well-formed for import - I would
1) start with a good dataset
2) verify that you can import it (I see you report that you can with the file open)
3) now close the file and try the code for transferspreadsheet on data known to be good
4) now try it on new data coming in from your source

Alex