Open Excel File From Access

[MGaddict]

So I'm trying to build a project and learning VBA from tutorials online. I did a lot of C++ back in the 90s, and still do on Arduino and what not. Otherwise, the only programming I've been doing for the last 10 years has been scripting in Linux. VBA in Access, needless to say, is outside my comfort zone.
I keep trying to use code from different tutorials and what not to figure this thing out, however, I keep running into the fact that apparently a lot of changes came with Access 2010 and even more with 2013. The vast majority of the tutorials online go back to 2003 - 2007 and that code is irrelevant.

I think my version numbers are all outdated and I can't figure out where to find the correct ones. I'm trying to open an xlsx file built in Office 365. The file name is provided by the aptly named FileName variable which is a string. I odn't get any errors, it compiles fine, but it just hangs on this for 5 minutes and does nothing. So I'm guessing I've got the Provider and Extended Properties all screwed up somehow with outdated version numbers.



Dim TeacherFile As New ADODB.Connection 'This somhow refrences where the recordset is coming from
Dim TeacherInput As New ADODB.Recordset 'This is the recordset we are going to be using

TeacherFile.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & FileName & "Extended Properties=""Excel 15.0;HDR=Yes"";"
Set TeacherInput = TeacherFile.Execute("[InputOfficeHours$A10:H63]")

So the FileName is a string. I don't totally get what's going on, but

[jonh]

Not sure why you felt the need to mark the other thread solved when it wasn't and start a new one.


Connection strings are a complete mystery to me and I've been programming databases for 20+ years.


Have a look here...
http://www.connectionstrings.com/




with regards to your previous thread



FileName = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="Excel Files *.xls* (*.xls*),")

'If the user didn't select anything, then oops, we got a problem. abort, abort!
If strFileToOpen = False Then
MsgBox "No file selected.", vbExclamation, "Sorry!"
Exit Sub

You are assigning the filename to variable FileName.
You then check to see if strFileToOpen is false but where does strFileToOpen get it's value?


A variable starting 'str' to me means it's text. How can text be False which is essentially numeric (0 or 1)?


Your IF statement does not end in and End If. How did that even compile?





strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="Excel Files *.xls* (*.xls*),")

'If the user didn't select anything, then oops, we got a problem. abort, abort!
If strFileToOpen = "" Then
MsgBox "No file selected.", vbExclamation, "Sorry!"
Exit Sub
end if


'Once we have a real file, open it already
TeacherFile.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
strFileToOpen & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
Set TeacherInput = TeacherFile.Execute("[InputOfficeHours$A10:H63]")
...

I'm not sure .execute will work either.
Execute usually means run a update query, whereas you seem to want to .openrecordset("table/query/range here").

[jonh]

Also VBA hasn't really changed in those 20+ years I've been using it so I'd be interested to see the tutorials you've been looking at.

[SamT]

So...

I'm going to delete the other thread now that jonh has picked up this one. I am also going to change this thread title to Open an Excel file from Access.

[MGaddict]

The original thread i opened was solved by taking your suggestion. Quite honestly what i was doing was trying to use outdated code to open a file box. While I could make that method work, it would have been convoluted so i had to find the newest way to do it which i did and that works, now I'm stuck here and not getting any errors.
This is my replacement for getting the file name:


Function getOpenFile()'This function will open the file open dialog box and allow the user to select a file
'The selected file is then returned
Dim fdialBox As FileDialog 'This object is the dialog box

Set fdialBox = Application.FileDialog(msoFileDialogFilePicker) 'This defines what type of box
With fdialBox
.Filters.Clear 'clear any random filters that exist
.Filters.Add "Excel Files", "*.xlsx" 'default to xlsx
.Filters.Add "All Files", "*.*" 'allow the user to change to *.*

If .Show Then 'This opens the dialog box and looks for a returned file
getOpenFile = .SelectedItems(1) '.SelectedItems is the file the user chose
Else
getOpenFile = "" 'if they didn't select anything, tell them
MsgBox "No File Selected"
End If
End With
End Function
So this is now my original function. Like I said before, my ultimate plan is to look at this table, parse through it, and enter the pertinent information to my database. If somebody knows an easier way to do that besides opening a recordset, I'm open to suggestions. Right now, I just wanted it to show some info from the recordset to prove it opened.


Private Sub Command0_Click()
Dim TeacherFile As New ADODB.Connection 'This somhow refrences where the recordset is coming from
Dim TeacherInput As New ADODB.Recordset 'This is the recordset we are going to be using
Dim fileName As String 'This is the name of the file being opened


fileName = getOpenFile() 'Use the function I built in the Module
MsgBox fileName
If fileName = "" Then GoTo ExitOnError

'Once we have a real file, open it already
TeacherFile.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & fileName & "Extended Properties=""Excel 15.0;HDR=Yes"";"
Set TeacherInput = TeacherFile.Execute("[InputOfficeHours$A10:H63]")


' Loop through the recordset and send data to the Immediate Window
TeacherInput.MoveFirst
Do
Debug.Print TeacherInput![Monday] & " " & TeacherInput![Tuesday] & " " & TeacherInput![Wednesday] & ""
TeacherInput.MoveNext
Loop Until TeacherInput.EOF


' Tidy up, This closes everythingout and releases the memory
ExitOnError:
' On Error Resume Next
TeacherInput.Close
TeacherFile.Close
Set TeacherInput = Nothing
Set TeacherFile = Nothing
Exit Sub


MsgBox "Hello"
End Sub

[MGaddict]

And by the way, that getOpenFile() is working perfectly.

[jonh]

Probably the easiest way to get at the data would be to link the sheet into access as a table or import the data into a temp table. Then you can read from it just like any other table.
You could also open a workbook and read the cells directly using automation.

Quick example to show both automation and ado...


Const CONNSTR As String = "Provider=%1%;Data Source=%3%;Extended Properties=""%2%;HDR=%4%"";"
Const Filename As String = "C:\test.xlsx"


Sub test()
Debug.Print "using ado...": Debug.Print
RS

Debug.Print "using automation...": Debug.Print
auto
End Sub


Sub auto()
With New Excel.Application
.Visible = True
With .Workbooks.Open(Filename)
For Each c In .Sheets(1).UsedRange
Debug.Print c
Next
.Close
End With
.Quit
End With
End Sub


Sub RS()

Dim cn As New ADODB.Connection, RS As New ADODB.Recordset
cn.Open stringer(CONNSTR, "Microsoft.ACE.OLEDB.12.0", "Excel 12.0 Xml", Filename, "YES")


RS.Open "SELECT * FROM `Sheet1$`", cn

'print headers
Debug.Print "#",
For Each f In RS.Fields
Debug.Print f.Name,
Next
Debug.Print

'print first 10 records
Do Until RS.EOF Or i = 10
i = i + 1
Debug.Print i,
For Each f In RS.Fields
Debug.Print f.Value,
Next
Debug.Print
RS.MoveNext
Loop
RS.Close
cn.Close
End Sub


Private Function stringer(s As String, ParamArray rep() As Variant) As String
stringer = s
For i = 0 To UBound(rep)
stringer = Replace(stringer, "%" & i + 1 & "%", rep(i))
Next
End Function

[MGaddict]

Thanks Jonh, I'm definatly going to have to play with that.
So for my orginal problem, This is the line I was suspecting, it took looking through a ton of posts to figure out what was supposed to be here.


'my original code. Note it's trying to use Jet and Excel 8 and the formatting is wrong. This was what I meant by outdated code
TeacherFile.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & FileName & "Extended Properties=""Excel 8.0;HDR=Yes"";"
Set TeacherInput = TeacherFile.Execute("[InputOfficeHours$A10:H63]")

'This is the latest declaration as best as I can figure, but I still don't fully understand it
TeacherFile.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & fileName & ";" & "Extended Properties=""Excel 12.0;HDR=Yes"";"
Set TeacherInput = TeacherFile.Execute("[InputOfficeHours$A10:H63]")
TeacherInput.MoveFirst
MsgBox TeacherInput![time]
MsgBox TeacherInput![Monday]
MsgBox TeacherInput![Tuesday]
etc...

So here is the problem now, I'm getting "94 Invalid use of Null" when it gets to the MsgBox. If I have .MoveFirst 2 times in row, I get the first MsgBox but then the second one gives the error. If I put .MoveFirst 3 times, I just get the original message box.
Somebody suggested .MoveLast and then .MoveFirst which gives me "-2147217884 Rowset does not support fetching backward."

One other thing, this is going to come up, is it possible to somehow


Dim Header1 As String
Header1 = "time"
'I know this s formatted wrong, but you get the point, I want to know how that should be formatted
TeacherInfo[Header1]