PDA

View Full Version : nervous! How To Import Data From SQL Query To VBA Coding?



lifeisanart
02-01-2016, 06:55 AM
Dear All,

How are you doing?

I want to import the data from the sql query to excel through vba. But I tried my code and turned out a totally blank of the sheet I want to see.

Here is code, could you please help to have a look for me since I have no clue after many corrections and test.

thanks very much.

Private Function GetFromDatabase(ByVal sSQL As String, ByVal sCell As String) As Boolean
On Error GoTo err_handler
Dim v As Variant
v = Application.Run("ExecuteQuery", sSQL)
Application.Run "CopytoExcel", v, sCell
GetFromDatabase = True
Exit Function
err_handler:
GetFromDatabase = False
End Function
Sub FullRefreshfromDatabase()
GetMainfromDatabase
GetMaturityfromDatabase
End Sub
Sub GetMaturityfromDatabase()
Sheets("raw_data").Columns("A:CZ").ClearContents
GetFromDatabase "exec qMaturity", "raw_data!A1"
End Sub
Sub GetMainfromDatabase()
Sheet2.Cells.Copy Destination:=Sheet1.Cells
End Sub

lifeisanart
02-01-2016, 06:58 AM
the area sheet "raw_data" is completely blank. :crying:

snb
02-01-2016, 07:01 AM
use '.copyfromrecordset'

lifeisanart
02-01-2016, 07:23 AM
thanks, I changed it and it reads "argument not optional"...
I guess the problem is in Sub GetMaturityfromDatabase()?
because the debug process just skipped this part

snb
02-01-2016, 10:24 AM
Too be honest: partial information .... partial answers.
Which SQL will be performed, from where, where do the data reside, etc.

lifeisanart
02-02-2016, 01:27 AM
Too be honest: partial information .... partial answers.
Which SQL will be performed, from where, where do the data reside, etc.
thanks for your reply.
sorry I am a beginner for that so I try to make it clearer:
in fact I want to get the query result from the database access 2010, inside which there is a query qmaturity extracted from the table which has a lot figures.
and I want to copy the data to the excel using vba...
that would be great if I can get some clues...

thanks

snb
02-02-2016, 02:01 AM
Use a querytable in Excel: Ribbon / Data / From Access

Aflatoon
02-02-2016, 09:32 AM
You haven't posted any of the relevant code - ie the ExecuteQuery or CopyToExcel routines.