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]

Ah, my apologies I had not checked my email the last few days. I noticed immediately that your file has NUMERIC values in column B Rows 1-2 but TEXT values in column B Rows 3-10. This is a common pitfall. However, the suggestions above should solve the issue - namely, to import to a staging table with only date/time and text fields.

I was able to successfully import your data from Access both by using the menus (without code) and by using the following code (where Sheet1 is the table name for the test table):

DoCmd.TransferSpreadsheet acImport, , "Sheet1", "C:\MyTemp\TestAHRM.xls", True

There are other solutions but the above is a general-purpose solution that I use whenever I don't have absolute control over the generation of the files I am using. Along with Norie, I'm a bit uncertain about the generation of these files but it looks like you've got mixed text/numeric values in a single column and that's problematic for Access imports.

 

[Norie]

Alex

If a 'staging' table is to be used it's probably better to use all text fields.

Importing date fields can be just as problematic as mixed numbers/text and the 'text' dates should be easily converted to real dates.

 

[arhm]

Alex,

Do you mean that you were able to import all the values that were in excel sheet into a new table "Sheet1" with no problem, just by using the TransferSpreadSheet Command.

I am just wondering, because when I used the same excel sheet and the same Transferspreadsheet command, I did not get all the values imported. There were null/empty values in tables. Could you please reconfirm by checking the content in the "Sheet1" table.

Or did you open the source excel sheet and changed the format of the column "B", or did you removed the errors in the column B, by clicking on "Ignore Error". Please let me know.

Thank You.
With Regards,
-A.R.H.

 

[Norie]

ARH

When you get null/empty values how exactly are you doing the import?

Are you importing to an existing table?

If so what fields do you have in the table and what type, eg Text, Number etc, are they?

 

[arhm]

Norie,

I use the simple Transferspreadsheet command. I import the data in a new table ( so the fields are automatically created by the kind of data being imported). The real problem is that the input excel has a column where in few rows entry has a numeric value, few has text.. this results in type conversion issue while importing.

If I had to import all the values successfully then, I should format the entire column of a particular data type. I simply open the execel sheet and click on ignore error, which treat that as a text value. Then when I import it is successful. I dont know how could i handle the same via a code. Thanks!!!

-A.R.H.

 

[Norie]

ARH

I really don't think you are getting the idea.

Import into an existing table where all the fields are Text.

When importing to a new table Access will try and 'guess' the data types, that's why you are getting the errors.

It won't do that for an existing table.

And I'm not sure about what you mean here.

If I had to import...

Formatting in Excel doesn't really have any affect on the underlying values.

If 'ignore error' refers to an option available in Excel I would be careful when using it as it may hide other errors.

 

[xenou]

Norie is correct, I created a table with all text fields. Actually took the table Access created the first time, changed all the fields to text, and then deleted the first "bad" import. After that, I use this table again.

In my last code example the name of the table was "Sheet1". Probably a very poor table name - happens to have the same name as the sheet with my test data. I'd normally give it a descriptive name like "Import_From_Excel_Temp"

 

[arhm]

Alex and Norie,
Thanks a lottttt... U were a real help .... I learnt a lott about VBA and excel via you guyz...

The requirement which I was working on at last got scrapped... The other system is going to send us the input file in Text instead of Excel .. as we found lot of testing issues in terms of the number of records that they are sending, the amount of data not fitting in the excel cells and lots more....

I spend consideravle amount of time for this learning more about VBA in excel world... I was little pissed due to the change in the project requirement... but then realized I got an oppurtunity to meet you guyz and learn more about VBA and excel... A BIG THANKS TO U...

Is there a means to to add any Karma points here in this site/forum against the solutions that we get for a helper/person....

-A.R.H.

 

[xenou]

Good to hear. I'm afraid we have to rely on real karma here ...

 

[arhm]

Lolz

A big Thank U !!!