VBA to find 1st Blank cell through Access/Excel integration VBA

[nagiese]

:banghead:Hello, I am new to VBA and I'm trying to write a VBA Module in Access that runs a SQL query and takes that result and appends it into an existing Excel report. Thus far I've been able to get it paste the data but it puts it in the wrong spot, the correct columns but wrong rows on Excel sheet. I'm looking for the correct VBA code that will look for the 1st available blank cell and then paste the data there. As each month the data will move down one row until the year is over then it will revert to the top and start all over again. Below is my VBA code and I've attached a copy of the report where the data is supposed to paste, for your review. I would greatly appreciate any help you could offer. Thank you in advance for your assistance.

P.S. The data is suposed to pase to cells F12-I12

Sub GetAccessData_With_SQL_GetObject_With_Excel()
'Step 1: Declare your Variables
    Dim MyConnect As String
    Dim MyRecordset As ADODB.Recordset
    Dim MyQueryDef As DAO.QueryDef
    Dim MyDatabase As DAO.Database
    Dim MySQL As String
    Dim MyRange As String
 
    Dim Db As Database
    Dim xl As Excel.Application
    'Set xl = New Excel.Application
    Set xl = CreateObject("Excel.Application")
    Dim xlwkbk As Excel.Workbook
    Dim xlsheet As Excel.Worksheet
    Dim strInput As String
    Dim strMsg As String
    'Sep 2: Declare your connection string
    MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info = False;Data Source= Q:\WWC Common\TAMPA Files\LSG Month-End FG Inventory Excess-Reserve Analysis.accdb; User ID = Admin;"
    'Step 3: Build Input Box
    strMsg = "What Fiscal Month?"
    strInput = InputBox(Prompt:=strMsg, Title:="Period")
    strInput = Chr(34) & strInput & Chr(34)
    MsgBox (strInput)
    'Step 4: Build Your SQL Statement
    MySQL = "Select [slq-Item count].[Fiscal Year],[slq-Item count].[Fiscal Month], " & _
            "Sum([slq-Item count].CountOfitem) As ItemCount,Sum([slq-Item count].[SumOfGross Units]) As Units " & _
            "From [slq-Item count] " & _
            "Group By [slq-Item count].[Fiscal Year],[slq-Item count].[Fiscal Month] " & _
            "Having([slq-Item count].[Fiscal Year]=2012 And [slq-Item count].[Fiscal Month]= " & strInput & ")"
    'Step 5: Instantiate and specify your recordset
    Set MyRecordset = New ADODB.Recordset
    MyRecordset.Open MySQL, CurrentProject.Connection    'adOpenStatic, adLockOptimistic)
    'Step 6: Instantiate Excel. If Excel isn't Loaded, Error # 429 occurs.
    Set xl = GetObject(, "Excel.Application")
    'Step 7: Open ItemIdCount.xlsx
    'Set xlwkbk = xl.Workbooks.Open("Q:\WWC Common\TAMPA Files\ItemIDCount.xlsx", , True)
    Set xlwkbk = xl.Workbooks.Open("Q:\WWC Common\TAMPA Files\ItemIDCount.xlsx")
    Set xlsheet = xlwkbk.Worksheets("ItemIdCnt")
    xl.Visible = True
    xlwkbk.Windows(1).Visible = True
    'Step 8: Find First empty Row and use that to build a dynamic range
    xlsheet.Select
    [COLOR=darkred]MyRange = "F" & _[/COLOR]
[COLOR=darkred]   ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row 1 [/COLOR][COLOR=teal](This is where I get the error message)[/COLOR]
    'Step 9: Copy the recordset to First Empty Row
    ActiveSheet.Range(MyRange).CopyFromRecordset MyRecordset
    'ActiveWorkbook.Save
    'ActiveWorkbook.Close
    'Step 10: e variables
    Set xlsheet = Nothing
    Set xlwkbk = Nothing
    Set xlApp = Nothing
    Set Db = Nothing
    MyRecordset.Close
 
 
 
 
End Sub
Sub ItemCount()
End Sub

 

[boblarson]

When posting code, especially long bits of code - USE CODE TAGS...

 

[nagiese]

Thank you, I'll remember that for next post.

:banghead:I'm really stuck on this section of the code I hope someone will please respond to my post.