Hi the community,
This code gives me an error 3343 at the line set MyDatabase
[/COLOR]</pre></pre>
This code gives me an error 3343 at the line set MyDatabase
Code:
[COLOR=#0000ff]Sub[/COLOR] RunParameterQuery()
[COLOR=#808080]'Step 1: Declare your variables[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] MyDatabase [COLOR=#0000ff]As[/COLOR] DAO.Database
[COLOR=#0000ff]Dim[/COLOR] MyQueryDef [COLOR=#0000ff]As[/COLOR] DAO.QueryDef
[COLOR=#0000ff]Dim[/COLOR] MyRecordset [COLOR=#0000ff]As[/COLOR] DAO.Recordset
[COLOR=#0000ff]Dim[/COLOR] i [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]Integer[/COLOR]
[COLOR=#808080]'Step 2: Identify the database and query[/COLOR]
[COLOR=#0000ff]Set[/COLOR] MyDatabase = Workspace([COLOR=#cc66cc]0[/COLOR]).OpenDatabase _
([COLOR=#ff0000]"C:\Users\hab\Filr\Air Traffic control.accdb"[/COLOR])
[COLOR=#0000ff]Set[/COLOR] MyQueryDef = MyDatabase.QueryDefs([COLOR=#ff0000]"Main Query"[/COLOR])
[COLOR=#808080]'Step 3: Define the Parameters[/COLOR]
[COLOR=#0000ff]With[/COLOR] MyQueryDef
.Parameters([COLOR=#ff0000]"[datexam]"[/COLOR]) = Range([COLOR=#ff0000]"D3"[/COLOR]).Value
.Parameters([COLOR=#ff0000]"[PTS engl]"[/COLOR]) = Range([COLOR=#ff0000]"D4"[/COLOR]).Value
[COLOR=#0000ff]End[/COLOR] [COLOR=#0000ff]With[/COLOR]
[COLOR=#808080]'Step 4: Open the query[/COLOR]
[COLOR=#0000ff]Set[/COLOR] MyRecordset = MyQueryDef.OpenRecordset
[COLOR=#808080]'Step 5: Clear previous contents[/COLOR]
Sheets([COLOR=#ff0000]"Main"[/COLOR]).[COLOR=#0000ff]Select[/COLOR]
ActiveSheet.Range([COLOR=#ff0000]"A6:K10000"[/COLOR]).ClearContents
[COLOR=#808080]'Step 6: Copy the recordset to Excel[/COLOR]
ActiveSheet.Range([COLOR=#ff0000]"A7"[/COLOR]).CopyFromRecordset MyRecordset
[COLOR=#808080]'Step 7: Add column heading names to the spreadsheet[/COLOR]
[COLOR=#0000ff]For[/COLOR] i = [COLOR=#cc66cc]1[/COLOR] [COLOR=#0000ff]To[/COLOR] MyRecordset.Fields.Count
ActiveSheet.Cells([COLOR=#cc66cc]6[/COLOR], i).Value = MyRecordset.Fields(i - [COLOR=#cc66cc]1[/COLOR]).Name
[COLOR=#0000ff]Next[/COLOR] i
MsgBox [COLOR=#ff0000]"Your Query has been Run"[/COLOR]
[COLOR=#0000ff]End[/COLOR] [COLOR=#0000ff]Sub[/COLOR]
[COLOR=#0000ff]