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

 

[Norie]

What makes you think you can use TransferSpreadsheet with an open workbook?

 

[arhm]

Alex,
I am deeply thankful for all your quick responses. I was working almost 24 hours on a streach, so had to hit d bed for few hours...

Well I agree, that TransferSpreadSheet comand works with an excel that u create manually, and what I suspect as u did is, that the source speardsheet which I get as an output from an intranet-website is in different format which is not being interpretted by the TransferSpreadSheet Command.... thus resultig in the error - "External table is not in the expected format"....
However, if I copy the data to another excel (not by save as, but manual copy) and feed that as input file then it works fine...
Should I b just trying to use another fresh spreadsheet to resolve this issue... or is there any way that I can just change the format of the original excel sheet via code... All your time, knowledge and kindness is truly appreciated.

Noorie - When i open that workbook and do a TransferSpreadsheet it works fine... even if u dont give the spreadsheet type argument.. still it works... what i assume is when that particular workbook is open and in memory then... the spreadsheettype gets overridden by the opened up workbook.. and thus the external table (spreadsheet) is recognized and executed...

 

[xenou]

It's a little difficult to troubleshoot your file. What follows is a guess.

1) I'm taking a different approach of:

a) open and copy the file you want to import,
b) paste the data to a new sheet 1 (as values)
c) close the file - giving it a temp name

2) Now I just run a transferspreadsheet on that temp file

3) It is assumed this code is in an Access module

Sub foo()
Dim xlApp As Object
Dim wb As Object
Dim wsOrig As Object
Dim wsNew As Object
Dim r As Object
Dim s As String

Set xlApp = CreateObject("Excel.Application")
'Set xlApp = New Excel.Application

xlApp.Visible = True '//a bit easier while debugging

With xlApp
    '//Open excel file to be processed
    s = MyGetOpenFileName("Select a File", xlApp)
    If Len(s) > 0 Then
        xlApp.Workbooks.Open s
        Set wb = xlApp.Workbooks(1) '//reference to excel file
    Else
        Exit Sub
    End If
    
    '-------------------------------------------
    On Error Resume Next 'for debugging
    Kill "C:\TEMP0034.xls" '//clean up temp file
    On Error GoTo 0
    '-------------------------------------------
    
    
    With wb
        .SaveAs "C:\TEMP0034.xls" '//save as a temp file
        .worksheets.Add Before:=wb.worksheets(1) '//New worksheet, at beginning
        .worksheets(2).Columns(1).Resize(, 4).Copy '//Copy Columns 1-4 of original first sheet to new first sheet
        .worksheets(1).cells(1, 1).PasteSpecial Paste:=12 '//values and number formats
        .Save
        .Close
    End With
    
End With

xlApp.Quit
        
'//Transfer data from new temp file, from new first sheet
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table1", "C:\TEMP0034.xls", True

'-------------------------------------------
'Kill "C:\TEMP0034.xls" '//clean up temp file - while debugging do not run this line so that we can inspect the results
'-------------------------------------------

End Sub
'--------------------------------------------------------------------
Public Function MyGetOpenFileName(ByVal strTitle As String, ByRef xlApp As Object) As String
'Gets a file Name and verifies the return value
Dim varFileName As Variant

varFileName = xlApp.GetOpenFilename(Title:=strTitle)

If (varFileName = False Or varFileName = "") Then
    MyGetOpenFileName = ""
Else
    MyGetOpenFileName = varFileName
End If

End Function

If this doesn't help I may ask you to send me a copy of the original file you are getting from your source as it may require some "laboratory analysis".

 

[arhm]

Hi Alex,

Thanks for all your helps.. truly appreciated...My apologies for not being connected all these days.. as I was ill and away from work...

I had few discussions with the other team from where we get the source input excel file... and learnt that they are generating that excel using .Net code where in the excel is generated using HTML tags.. I think thats the reason why the Transferspreadsheet command is not recognizing the format of the external table.

However, your idea was good, but it required little change to work in my scenario; I had to open a new excel workbook and then copy the content from source work book to the new one and then fed the new workbook to the TransferspreadSheet command and it worked.
Hurray!!! Hurray!!!

Here I have question out of enthusiasm to learn more ... Is there any straight way to get the HTML written EXCEL file to import/export the data without copying its content to some other workbook..

****************************
I got successful in importing data from excel to Access MDB. Now, I am facing a new problem... few of the cells do not get imported in to table ... when i looked in to the error log found that theer was a "type conversion" problem with them.. When i looked closely in those cells in the source file, I found that they had some error tag on it stating " Number is stored in text format". I think now I should remove that error by typeconverting to Text/Number, and then import.... could u pls help me on how to type copnvert a whole comun in excel using VBA...

Thanks a lottt for helping me in this regard..
-A.R.H

 

[xenou]

Ah, well there's certainly a few monkey wrenches here. That's good you have it working.

Normally, I would first transfer the data into Access first to another table (a staging table). This table will have all fields defined as Text or Date/Time. That way, all your data, regardless of type, should transfer in. Next I would append this data to the "real" table. That may involve two steps now instead of one (import data to staging table, then append data from staging table to destination table). But it typically runs without errors, and you will get all the data in the staging table. Actually, the first step before these two is to delete the existing data in the staging table, so its a 3-step process, 1) clear staging table, 2) import new data to staging table, 3) append from staging table to destination table.

Hope this helps,

Alex

 

[Norie]

I think you should find out exactly how this file is being generated.

If it can be generated in HTML then I'm pretty sure it can be generated as an Excel spreadsheet or in some other format that's more compatible.

 

[arhm]

Alex - I exactly did the same way as you said. It appears that we think alike ... Well the true problem is when u have any excells whose dataype is not clearly defined those cells will not get imported by transfertextsheet command. So the dat will come in to the staging table.

Now the true problem is, I have to defnie a dataype of a column in excel using VBA.. I got the below code but it is not working.... EXcel.Range is not being recognized by the compiler... I need to get the below code work now....

*********************************************
Dim ws1 As Excel.Worksheet
Dim aCell As Excel.Range

'... ws1 is your worksheet

With ws1.UsedRange
For Each aCell In .Cells
If Excel.Application.WorksheetFunction.IsNonText(aCell) Then
If aCell <> "" Then aCell.Formula = "'" & aCell.Value
End If
Next
End With

**************************************************


Norie- you are right, The team which provides the excel input file should give the data in the desired native excel format with error free cells to us; but seems they dont wanna do it.. aaahh grrrr..... Anyways so I am doing all that job at my end..



Alex and norie - Thanks for ur continuous help.

 

[Norie]

What is that code meant to do?

You can't really set a data type for cells/ranges/whatever in Excel, and if you use Alex's idea you shouldn't need to.

As I see it what you do is import into this 'staging' table, then do any data type conversion necessary, perhaps putting the results in a new table.

There are plenty of functions in Access to do that, and once you have the data typed correctly etc you can proceed to append it to the existing table.

All of this could be done stage by stage, and should be at first, but once you've got the process up and running you might be able to refine it.

As to the data you are receiving, I'd like to know a bit more about it.

As far as I'm aware you can't create an actual Excel file using HTML, using .NET or other technology.

You can create HTML files from data but they aren't Excel files.

If they can't supply the data in a form that you can use without you having to jump through hoops I think it's time you had a word with someone.

When you are perhaps you could point out the possible errors that could occur.

 

[xenou]

Dim ws1 As Excel.Worksheet
Dim aCell As Excel.Range

'... ws1 is your worksheet

With ws1.UsedRange
For Each aCell In .Cells
If Excel.Application.WorksheetFunction.IsNonText(aCell) Then
If aCell <> "" Then aCell.Formula = "'" & aCell.Value
End If
Next
End With

It might be a matter of which Excel version is involved here. If you are automating Excel from Access, its best to write the code with "Early Binding" (dim appXL as Excel.Application, Dim rng as Excel.Range, etc.), but run the code with "Late Binding" (Dim appXL as Object, Dim rng as Object). That way the compiler can resolve the object libraries with "whatever's available" so to speak (I'm not sure of the correct terminology).

Ex.

Sub Foo()
Dim xlApp as Object
Dim xlWb as Object
Dim xlWs as Object
Dim xlRng as Object

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open "C:\MyBook.xls"
Set xlWs = xlWb.Worksheets(1)

[COLOR="SeaGreen"]'//Do stuff with worksheet[/COLOR]

xlWb.Close SaveChanges:=False
xlApp.Close
Set xlApp = Nothing

End Sub

I agree with Norie that you shouldn't have to resort to text markers etc. - numbers should pull in just fine to a staging table, as described above - but its clear you've got plenty of problems with these files, eh? Where there's a will there's a way.

 

[arhm]

I think its time that I should eMail you a sample/example excel sheet, in which few cell-values will not get imported. Can you please give me your eMail id to which I can eMail the excel sheet.