Code suggestions [Resolved]

[hatemachine]

First of all, I looked at VB for the first time just Thursday, so excuse my ignorance. If you have any links to good tutorial sites, that would be great. Until then, here is my question. I got my VB code by recording a macro. It imports a search query and displays the results to cell A1. I would like it to display into the first row with no data in it though. I'm not sure how to do this. Right now, my query asks for a certain database number, so if I have to import multiple database numbers to the same sheet, I have to run it three times. But with this code, it will overwrite the previous database. After I figure this out, I'll be working on looping this thing for a certain amount of times. But for the time being, just not erasing the data would be better than what I'm having to do now.


Sub Macro2()

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\test\Query from MS Access Database.dqy" _
, Destination:=Range("A1"))
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False

End With
Application.Goto Reference:="Macro2"
End Sub

[RobDog888]

Welcome to the Forums.

I'm a little unclear on your question. You say that you want to place the non
data in a row "I would like it to display into the first row with no data in it though."

Also, when it places the data in A1 for the first loop, where do you want the
next data placed.

[hatemachine]

Oh... sorry about that. So let's say I have a blank spreadsheet. I want the data to be placed in A1. After that, the data may take up 3 rows (but it varies). So the next loop, I want it in cell A4 (for this example). If I re run the macro as is, it will replace the data in A1 instead of appending it to the next available row. Does that make sense?

So I need to figure out how to put the data into the first row in my spreadsheet that has no data in it. I read somewhere that I need to use something like this:

ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

Got that from here (http://www.vbforums.com/showthread.php?t=234942&highlight=LastCell)

[RobDog888]

Yes that is the code you need to place the query results in the next available row.

[hatemachine]

I tried replacing this line:

(Connection:= _
"FINDER;C:\test\Query from MS Access Database.dqy" _
, Destination:=Range("A1"))
with

(Connection:= _
"FINDER;C:\test\Query from MS Access Database.dqy" _
, Destination:=ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row)


That did not work. And I would guess it has to do with my lack of programming knowledge as to why it did not work. Do I need some other function to use this?

[RobDog888]

Since your query is returning a single field value, you need to specify the cell
(range) for your query results to be placed into, not just an entire row.
Dim sCell as string
sCell = "A" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
(Connection:= _
"FINDER;C:\test\Query from MS Access Database.dqy", Destination:=sCell) 'A53 for exampleHTH

[hatemachine]

Thanks... Problem Solved


Sub Macro2()
Dim sCell As String
sCell = "A" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\test\Query from MS Access Database.dqy" _
, Destination:=Range(sCell))
.Name = "Query from MS Access Database"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Cells.Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub



Now trying to figure out how to edit the original subject to resolved.

[RobDog888]

No prob. Glad to help. :thumb: