-
July 2nd, 2012, 08:03 AM
#1
Open excel spreadsheet as database
Hello, another problem.
I have VBA code which will open a csv file as a database. This works fine, and is included below:
Code:
With DBEngine.OpenDatabase( PathName, False, False, "Text;" )
With .OpenRecordset( FileName )
While Not .EOF
MsgBox( !FieldData )
.MoveNext
Wend
.Close
End With
.Close
End With
I'm now trying to open an xls file the same way, by changing the "Text;" command to "Excel 8.0;"
I'm getting error '3051': The msft access database engine cannot open or write to the file 'PathName'. It is already opened exclusively by another user, or you need permission to view and write it's data.
(If I leave it as "Text;" then I get error "cannot update. Database or object is read-only.")
I'm not in the file, and don't want to write to it, only read from it. Does anyone have an idea what I'm doing wrong?
-
July 2nd, 2012, 08:19 AM
#2
Re: Open excel spreadsheet as database
Never mind, I've solved it like so:
Code:
With DBEngine.OpenDatabase( PathName & FileName, False, False, "Excel 8.0;" )
With .OpenRecordset( "SheetName$NamedRange" )
While Not .EOF
MsgBox( !FieldData )
.MoveNext
Wend
.Close
End With
.Close
End With
The pathname needed to be the full path and filename of the spreadsheet, and filename needed to be the sheetname and the named data range for the required data.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|