Hi,

Does anyone have any ideas/tools for cleaning up the excel
workbooks to be able to import into Microsoft Access.

I am trying to import from some workbooks that are Excel 2003 to a new table
in Access 2003. The excel files have column headings and a lot of data. I
receive an error message trying to use the transferspreadsheet macro - all
the error says is action failed. Any idea how I can clean up the excel
workbook to be able to import into access. I am not sure how or where the
Excel workbook was created.

Thanks

Peter

Verify that the column headings conform to Access fieldname requirements and
that the data in columns is consistent from beginning to end. If row 1 in
column C is a fieldname, row 2 in column C is a long integer and row 123 in
column C contains text, it will pose a problem.

Additionall, open the workbook and go to the sheet that contains the data
you're trying to implort and press <Ctrl + <End to determine that the
defined data range doesn't include blank rows and columns. If the data range
is A1 through K2000 and the last cell is Z5000, you'll need to resize the
data range.

To reset the sheets data range, select all unused column and click
EDIT/DELETE from the menu to clear the blank column range. Repeat the same
process for all the blank rows to clear the blank row ranges. Resave the
file and close the file and try your import macro again, or just do it
manually by clicking FILE, GET EXTERNAL DATA, IMPORT. Locate the workbook
file and select it and then follow the on-screen steps to import the data.

--
Kevin Backmann


"PAG" wrote:

Hi,

Does anyone have any ideas/tools for cleaning up the excel
workbooks to be able to import into Microsoft Access.

I am trying to import from some workbooks that are Excel 2003 to a new table
in Access 2003. The excel files have column headings and a lot of data. I
receive an error message trying to use the transferspreadsheet macro - all
the error says is action failed. Any idea how I can clean up the excel
workbook to be able to import into access. I am not sure how or where the
Excel workbook was created.

Thanks

Peter

Thank You

"Kevin B" wrote:

Verify that the column headings conform to Access fieldname requirements and
that the data in columns is consistent from beginning to end. If row 1 in
column C is a fieldname, row 2 in column C is a long integer and row 123 in
column C contains text, it will pose a problem.

Additionall, open the workbook and go to the sheet that contains the data
you're trying to implort and press <Ctrl + <End to determine that the
defined data range doesn't include blank rows and columns. If the data range
is A1 through K2000 and the last cell is Z5000, you'll need to resize the
data range.

To reset the sheets data range, select all unused column and click
EDIT/DELETE from the menu to clear the blank column range. Repeat the same
process for all the blank rows to clear the blank row ranges. Resave the
file and close the file and try your import macro again, or just do it
manually by clicking FILE, GET EXTERNAL DATA, IMPORT. Locate the workbook
file and select it and then follow the on-screen steps to import the data.

--
Kevin Backmann


"PAG" wrote:

Hi,

Does anyone have any ideas/tools for cleaning up the excel
workbooks to be able to import into Microsoft Access.

I am trying to import from some workbooks that are Excel 2003 to a new table
in Access 2003. The excel files have column headings and a lot of data. I
receive an error message trying to use the transferspreadsheet macro - all
the error says is action failed. Any idea how I can clean up the excel
workbook to be able to import into access. I am not sure how or where the
Excel workbook was created.

Thanks

Peter