import spreadsheet file: define range

[santosh1]

Hello,

I am trying to import spreadsheet file into a table using the TransferSpreadSheet function. I want to define range which keeps changing for different files. How can I do this
in Access VBA? I am using the following code.

I will appreciate your help very much. Thanks.

DoCmd.TransferSpreadsheet acImport, nSpreadSheetType, "SPECTRUM_CALCULATION", _
cFileName, Me!ChkBox_FieldNames, range

Santosh

 

[gwinn7]

You can use the Excel object model to program against. Here is some sample code to get you thinking about it. Also, look up the VBA help on this topic; possibly check the MS Knowledge Base for examples.

Add the Excel object library to your references:
Tools\References

Next, you can apply some code something like this...

Dim X as Excel.Application
Dim R as Excel.Range
Set X = New Excel.Application

X.Visible = False
X.OpenWorkBook(???)
Set R = X.ActiveWorksheet.Range(???)

Then you should be able to loop through the range and pull your values.

Gary
gwinn7
A+, Network+