how to delete rows in linked spreadsheet

[Guest]

Hello

I have a linked Excel spreadsheet from which I am able to update fields in
an Access database. The spreadsheet has a number of rows at the top which
come before the header rows and which cause a problem if I dont delete them
manually before running my code. The format of the spreadsheet as supplied
to me cannot be changed. I would like to know if it is possible to delete
these rows programatically, and if so, how?

Thanks for any help.

 

[Steve]

Use Transferspreadsheet to import the header rows and the data below these
rows into a temp table. You can do this by specifying the range in the Excel
workbook you want to import. Then after running your code, delete the temp
table.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Keith Wilby]

Hello

I have a linked Excel spreadsheet from which I am able to update fields in
an Access database. The spreadsheet has a number of rows at the top which
come before the header rows and which cause a problem if I dont delete
them
manually before running my code. The format of the spreadsheet as
supplied
to me cannot be changed. I would like to know if it is possible to delete
these rows programatically, and if so, how?

Thanks for any help.

You could use Office Automation VBA code to interrogate your spreadsheet. I
have some automation code samples if you want them.

Regards,
Keith.
www.keithwilby.com

 

[Guest]

Thanks Keith I would be interested to see them.

Regards

 

[Keith Wilby]

Thanks Keith I would be interested to see them.

First, set a reference to the Excel object library in a class module.
Here's a sample of code which opens an Excel file (hidden) and sets "Sheet1"
as the active worksheet:

Dim strFilePath As String
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet

strFilePath = "X:\MyPath\MyFile.xls"

Const conSHT_NAME = "Sheet1"

Set objXL = New Excel.Application
objXL.Visible = False
Set objWkb = objXL.Workbooks.Open(strFilePath)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
Err.Clear

You can then use "objSht" to read and manipulate the cells on Sheet1.

MsgBox objSht.Cells(1,1)

will display a message box with the contents of cell A1.

Here's some tidying up code. Bear in mind that if your code bombs out
before it closes Excel then you'll have to close the session using Task
Manager.

objXL.ActiveWorkbook.Save
objXL.Quit

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

Hope that's enough to get you started.

Keith.
www.keithwilby.com