Import from Excel on first sheet regardless of name 1

[Zeroanarchy]

Hi, Does anyone know if you can import data from an excel file based on the first sheet regardless of name.

For example I am using this as the current code

DoCmd.TransferSpreadsheet acImport, , "Raw Material Prices", "c:\filename.xls", True

What I want to be able to do is, instead of the sheet name being" Raw Materials Prices" If the user of the xls changes the name I still want to be able to import it.

I tried leaveing the sheet name out but it doesn't work.

Any ideas??

Cheers


ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[SkipVought]

Hi,

Use the GetObject to open the workbook.

...
Set oWB = Getobject("c:\filename.xls")
sSheetName = oWB.Sheets(1).name
oWB.close
set oWB = nothing
...

Skip,

_[red][/red]

 

[Zeroanarchy]

Thanks Skipvought works well. I do have another question is there anyway of checking to see if the sheet has any records in it prior to importing?

Thanks

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[SkipVought]

...
Set oWB = Getobject("c:\filename.xls")
With oWB.Sheets(1)
    sSheetName = .name
    With .UsedRange
      lRowCount = .Rows.Count
      iColCount = .Columns.Count
    End WIth
End With
oWB.close
set oWB = nothing
...

Skip,

_[red][/red]

 

[Zeroanarchy]

Thanks SkipVought,

I tried the code but came up with an unusually problem.

I deleted the records in an excel spreadsheet, saved the file and then ran a test to count the records. What was unusual was that running the above code, counts null records as records.

I thought maybe the workaround might be to check to see if a value exists in a specific row say B1.

Because the field is an active field but it is empty, does this mean that checking the value of the field returns False on Null ?

eg. If Row2 Col B is null then
Msgbox true
Else
Msgbox False.

And if this would work as a workaround, how would I go about check a value in a field.

Thanks

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[SkipVought]

You ran into an anonymoly of sorts in Excel.

The UsedRange property can give undesired results in a session. However, if you saved, closed and reopened the workbook, I believe you would not get the same results.

The UsedRange property can give undesired results when you use the Clear or ClearContents Methods. However, if you Delete/Shift Rows/Columns, you will get accurate results in the same session.

Skip,

_[red][/red]

 

[Zeroanarchy]

Hey SkipVought thanks for your help, I found that when I used the delete option, and I looked at the view in preview, I had a stack of empty fields. So I came up with a work around.

I use the first option to bring in the first sheet as a table. Then I checked the table for certain fields, finishing with a count on the records in the table.

If certain criteria where not met then error message.

Thanks again for your help SkipVought.

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.