Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ewalters4
Starting Member
1 Post |
Posted - 2012-12-13 : 16:50:47
|
Hello all. I hope someone can help.Below is the code for a test MACRO where I import worksheets and query them. I have the code to pull the information in from any users desktop in VBA but I cannot figure out how to get the SQL statements to work in the query. I need the data pulled from the any users workbook file and queried. The data file location is the same for all users, desktop\TEST folder, but the drive and user name will be different.Sub Code_tester()Dim wbDst As WorkbookDim wbSrc As WorkbookDim wsSrc As WorksheetDim MyPath As StringDim strFilename As StringApplication.DisplayAlerts = FalseApplication.EnableEvents = FalseApplication.ScreenUpdating = FalseDim oWsh As ObjectDim sPathDesktop As StringSet oWsh = CreateObject("WScript.Shell")sPathDesktop = oWsh.SpecialFolders("Desktop") & "\TEST folder"Set wbDst = Workbooks.Add(xlWBATWorksheet)strFilename = Dir(sPathDesktop & "\PET*.xlsx", vbNormal)If Len(strFilename) = 0 Then Exit SubDo Until strFilename = ""Set wbSrc = Workbooks.Open(fileName:=sPathDesktop & "\" & strFilename)Set wsSrc = wbSrc.Worksheets(1)wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)wbSrc.Close FalsestrFilename = Dir()LoopwbDst.Worksheets(1).Name = "Combined"ChDir sPathDesktopActiveWorkbook.SaveAs fileName:=sPathDesktop & "\Combined", _FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ReadOnlyRecommended:=False, CreateBackup:=FalseSheets("Combined").SelectRange("A1").SelectWith ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _"ODBC;DSN=Excel Files;DBQ=d:\data\User Me\Desktop\TEST folder\Combined.xls;DefaultDir=d:\data\User Me\Desktop\TEST folder;DriverId=10" _), Array("46;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("$A$1")) _.QueryTable.CommandText = Array( _"SELECT `'Sheet1 (2)$'`.`sale #`, `'Sheet1 (2)$'`.`complaint #`, `'Sheet1 (2)$'`.`Complaint narrative`, `'Sheet1 (2)$'`.`Complaint resolution`, `'Sheet1 (2)$'`.`date of resolution`, `'Sheet1 (3)$'`.`sa" _, _"le #`, `'Sheet1 (3)$'`.animal, `'Sheet1 (3)$'`.disposition, `'Sheet1 (3)$'`.color, `'Sheet1 (3)$'`.`date of sale`" & Chr(13) & "" & Chr(10) & "FROM `d:\data\User Me\Desktop\TEST folder\Combined.xls`.`'Sheet1 (2)$'` `'Sheet1 (2)$" _, _"'`, `d:\data\User Me\Desktop\TEST folder\Combined.xls`.`'Sheet1 (3)$'` `'Sheet1 (3)$'`" & Chr(13) & "" & Chr(10) & "WHERE `'Sheet1 (2)$'`.`sale #` = `'Sheet1 (3)$'`.`sale #`" & Chr(13) & "" & Chr(10) & "ORDER BY `'Sheet1 (2)$'`.`sale #`" _).RowNumbers = False.FillAdjacentFormulas = False.PreserveFormatting = True.RefreshOnFileOpen = False.BackgroundQuery = True.RefreshStyle = xlInsertDeleteCells.SavePassword = False.SaveData = True.AdjustColumnWidth = True.RefreshPeriod = 0.PreserveColumnInfo = True.ListObject.DisplayName = "Table_Query_from_Excel_Files".Refresh BackgroundQuery:=FalseEnd WithEnd SubI need it to be able to look in any users desktop\TEST folder to run the query.Any suggestions? |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-12-13 : 20:12:04
|
This is not about SQL Server. Please post your question in an Excel / VBA in one of the Microsoft forum http://social.microsoft.com/Forums KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
|
|
|
|