Use worksheet functions in VBA w/out opening sheet

[asingh]

Hi,

I want to run some worksheet functions in VBA, on some excel spread sheets, without opening them.

The VBA will run off Access. Basically I need to use the Worksheet functions, primarily MATCH, to trap where my column headers are placed on the spread sheet, and from there get coordinates, and supply those to "transferspreadsheet" macro in access, to directly port data in the access data base.

I can do this easily by opening the worksheet, but would it be possible by not opening the worksheet. I supply the worksheet name, create an excel object reference in access VBA, and run the MATCH function...??

Thanks a lot for the help.

regards,

asingh

[malik641]

Hi asingh,

I'm not clear on what you're exactly trying to do, but if you are looking to use Excel's Match function, without actually Opening a workbook you can do that.

Public Sub MySub()
Dim xlApp As Excel.Application
Dim arr1(0 To 2) As String
Dim dblMatchPosition As Double

Set xlApp = New Excel.Application

arr1(0) = "a"
arr1(1) = "b"
arr1(2) = "c"

dblMatchPosition = xlApp.WorksheetFunction.Match("b", arr1(), 0)

Debug.Print dblMatchPosition ' Returns 2, the relative position of the array arr1()
End Sub

[asingh]

Hi..

Apologize if I was not clear....

This is what I want to do.

I have an excel sheet, which has column headers say at row 6, with data below these column headers. Then there is a gap of around 1-2 rows..and aother set of column headers...and again data below this 2nd set of column headers.

I need to get this data into access tables. Since there are two different sets of column headers, I should ideally move it into 2 differnt tables.

The logic I had thought:
The amount of data below the first column header can be dynamic, this would cause the 2nd column header position to be dynamic, so I can not hard code these coordinates into vba.

1. Use a match function on the excel spread sheet, and get the starting position of the 1st column headers. And also get the end row, using a combination of xldown. This would be my first data set, this excel coordinates I can provide to Access via vba and the data will transfer into access.

2. Again use a match function and get the starting position of the 2nd column header, and similarily find the last data point. Again I supply these coordinates to Access via VBA and the data will transfer into access.

So I want to run a MATCH function on a worksheet from Access, without opening the worksheet. Is it possible.

thanks and regards,

asingh

[Bob Phillips]

If you want to use worksheet functions on a worksheet, it has to be open. If you want to not open it, you will need to construct a SQL query and use ADO.

[asingh]

XLD...and reference..lookup..where I could start...with the ADO option....

thanks...

[Bob Phillips]

Personally, I wouldn't bother, I would just open the file.