Adding Unnecessary Blank row on import from Access 2010 (Windows 7)

nstratton

New Member
Joined
Sep 17, 2015
Messages
3
Code:
 Private Sub cmdCreateChart_Click()Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim objXL As Object
Dim lngLastDataRow As Long


Set objXL = CreateObject("Excel.Application")
Set dbs = CurrentDb


'Get the parameter query
Set qdf = dbs.QueryDefs("qryCharts")


'Open a Recordset based on the query
Set rst = qdf.OpenRecordset()
With objXL
  .Visible = True
  .UserControl = True
   With .Workbooks.Open("C:\Users\cole.stratton\Documents\Form1")
     lngLastDataRow = .Worksheets("ChartData").Cells.SpecialCells(11).Row
     .Worksheets("ChartData").Range("A" & CStr(lngLastDataRow + 1)).CopyFromRecordset rst
     .Worksheets("ChartData").Range("A:D").RemoveDuplicates Columns:=4


   End With
End With
 
rst.Close
 
objXL.WindowState = xlMaximized
End If
Set rst = Nothing
Set objXL = Nothing
End Sub
I am using the code above to bring data into an excel workbook from an access database to create charts (since Access charting is an absolute pain). There are no errors occurring, however when the code is run a blank line is being inserted before the import data instead of the data being added directly under the existing data. Any ideas what might be making this happen?
 

Gingertrees

Well-known Member
Joined
Sep 21, 2009
Messages
697
CStr(lngLastDataRow + 1

You're telling it to paste it below your last row PLUS 1. :)

This probably means the "last row" is selecting the row below your actual last row of data.
 
Last edited:

nstratton

New Member
Joined
Sep 17, 2015
Messages
3
I noticed it as soon as I posted the question. I feel like a moron. Took out the +1 and of course it works like expected.
I appreciate the quick response
 

Forum statistics

Threads
1,084,880
Messages
5,380,425
Members
401,677
Latest member
BobH

Some videos you may like

This Week's Hot Topics

Top