March 28, 2011 at 8:36 am
Hi,
First, im not sure if this is the correct subforum in which to post this. Sorry if its the wrong one.
I am writing this simple script. It gets data from two excel files and pastes them into a third one.
By excel macro, it first copies the two excel files into a single one with two sheets. Then, by SQL i run a query to create a third one. Finally, i run an SQL query to get data from a table and paste it into the last one.
When the query is "INSERT INTO ... VALUES ..." it works fine. But when it is "INSERT INTO ... SELECT * FROM ..." it pastes the data into one of the source tables instead of the new one. It even pastes it into the correct columns, but yet in a wrong table.
I´m using this connection string:
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;"
sConn = sConn & "Data Source=" & sTempFile & ";"
sConn = sConn & "Extended Properties=Excel 12.0;"
I have uploaded it. It contains the file with the macro and the two source files. You just need to unzip and edit the folder path in the very first lines of the macro SQLquery.xlsm.
http://www.2shared.com/file/UoZ3CV72/SQL_excel_query.html
I would really appreciate if someone could take a look. Im stuck at work with it and i need to get it done.
Thanks!
March 28, 2011 at 8:42 am
I can not get to the file you uploaded as the site is blocked on my work network. Can you paste the SQL code that is used?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 28, 2011 at 8:48 am
Here it goes. Thanks for checking.
It is actually in VB in an excel macro.
'Original file paths. These will be set automatically once the macro is added to the script.
Const sFileFolder = [TYPE FOLDER PATH HERE] & "\" 'Folder containing the files.
Const sFileRED = sFileFolder & "RED1.xls" 'Results file from script
Const sFileBLUE = sFileFolder & "BLUE1.xls" 'Results file from script
Const sTempFile = sFileFolder & "Results_Join.xlsx" 'Final file with the query results
Const sSheetCSKS = "RED"
Const sSheetCSKT = "BLUE"
'SQL query code
Const sCreateTableSQL = "CREATE TABLE QueryResults " _
& "([COL1] char(50)," _
& "[COL2] char(50)," _
& "[COL3] char(50)," _
& "[COL4] char(50)," _
& "[COL5] char(50)," _
& "[COL6] char(50)," _
& "[COL7] char(50))"
'SQL query code VALUES - IT WORKS
'Const sQuery = "INSERT INTO QueryResults (COL1,COL2,COL5,COL7) VALUES ('abc','def','ghi','jkl')"
'SQL query code SELECT FROM - DOESNT WORK. It inserts the results in BLUE table
Const sQuery = "INSERT INTO QueryResults (COL1,COL2,COL5,COL7) SELECT * FROM [BLUE$]"
Sub SQLJoin()
'Delete temp file if it exists
test = Dir(sTempFile)
If Not test = "" Then
Kill sTempFile
End If
'Call Excel
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False
objExcel.Visible = True
objExcel.Application.ScreenUpdating = True
'Open source files and open a third one as temporary file
Set objWbA = objExcel.Workbooks.Open(sFileRED)
Set objWbB = objExcel.Workbooks.Open(sFileBLUE)
Set objWbC = objExcel.Workbooks.Add()
objWbC.SaveAs sTempFile
'Copy both source files to the temporary file
objWbA.Worksheets(1).Copy objWbC.Worksheets(1)
objWbB.Worksheets(1).Copy objWbC.Worksheets(1)
objWbC.Save
'Close all
objWbA.Close
objWbB.Close
objWbC.Close
'Open temp file
Set objWorkbook = objExcel.Workbooks.Open(sTempFile, , False)
'Rename sheets and delete the other ones
objWorkbook.Worksheets(2).Name = "RED"
objWorkbook.Worksheets(1).Name = "BLUE"
objWorkbook.Worksheets("Sheet1").Delete
objWorkbook.Worksheets("Sheet2").Delete
objWorkbook.Worksheets("Sheet3").Delete
'Set "Text" format to all cells, Autofit width of all columns and save
objWorkbook.Worksheets("BLUE").Activate
objWorkbook.Worksheets("BLUE").Cells.Select
objWorkbook.Worksheets("BLUE").Cells.NumberFormat = "@"
objWorkbook.Worksheets("BLUE").Cells.EntireColumn.AutoFit
objWorkbook.Worksheets("RED").Activate
objWorkbook.Worksheets("RED").Cells.Select
objWorkbook.Worksheets("RED").Cells.NumberFormat = "@"
objWorkbook.Worksheets("RED").Cells.EntireColumn.AutoFit
objWorkbook.Save
objWorkbook.Close
'Establish connection with database (temp.xlsx in this case)
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;"
sConn = sConn & "Data Source=" & sTempFile & ";"
sConn = sConn & "Extended Properties=Excel 12.0;"
'Connect to the database
Set dbexample = CreateObject("ADODB.Connection")
dbexample.Mode = 3
dbexample.Open (sConn)
'Perform the query
dbexample.Execute (sCreateTableSQL)
dbexample.Execute (sQuery)
dbexample.Close
' Close the database connection and clean the objects
Set dbexample = Nothing
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing
Set objWbA = Nothing
Set objWbB = Nothing
Set objWbC = Nothing
End Sub
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply