Hi all, I've scored the forums and are yet to find any other instances of this problem. So here goes...
The company I work for is going though a growth spurt with regard to reporting requirments. I've recently been tailoring reports to suit as many as 30 new types of report. As our management team prefer using Excel, i've created many automated worksheets which run a various of SQL scripts in the background.
Currently im faced with the task of creating a worksheet in which staff would manually enter product barcodes (as many as 80 products) , for my script to pick up and collect sales data upon.
While I managed to successfully create a small version allowing the use 25 products, any more lines caused the macro to fail as there were too many characters in the unbroken line or something.
The data I collect is captured from many tables and are aften quite long in length. I normally simply record the macro while i connect to ODBC and edit the query in MS Query. It won't even record the full query. I've tried writing the code manually with the correct syntax but it stops me at a certain point because the script again contains too many characters.
I'd like to know...
1) is there an easier way connect to ODBC and run a specific query regardless of its size?
2) is there a better way to collect variable data (like my 80 products) from a range in excel.
I would so greatly appreciate assistance with this issue. It's really doing my head in now.
Thank you for all your great work. This site helps many of us keep our jobs and sometimes look really clever to boot.
example scripts below:
In this (recorded) macro i am collecting barcodes for randomly entered products...
myvar01 = Range("Promo_Items!A2")
myvar02 = Range("Promo_Items!A3")
myvar03 = Range("Promo_Items!A4")
myvar04 = Range("Promo_Items!A5")
myvar05 = Range("Promo_Items!A6")
myvar06 = Range("Promo_Items!A7")
myvar07 = Range("Promo_Items!A8")
myvar08 = Range("Promo_Items!A9")
myvar09 = Range("Promo_Items!A10")
myvar10 = Range("Promo_Items!A11")
myvar11 = Range("Promo_Items!A12")
myvar12 = Range("Promo_Items!A13")
myvar13 = Range("Promo_Items!A14")
myvar14 = Range("Promo_Items!A15")
myvar15 = Range("Promo_Items!A16")
myvar16 = Range("Promo_Items!A17")
myvar17 = Range("Promo_Items!A18")
myvar18 = Range("Promo_Items!A19")
myvar19 = Range("Promo_Items!A20")
myvar20 = Range("Promo_Items!A21")
myvar21 = Range("Promo_Items!A22")
myvar22 = Range("Promo_Items!A23")
myvar23 = Range("Promo_Items!A24")
myvar24 = Range("Promo_Items!A25")
myvar25 = Range("Promo_Items!A26")
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = _
"ODBC;DSN=WHSE;Description=RMS Sales;APP=Microsoft Office 2003;WSID=ITUSER;DATABASE=WHSE;Trusted_Connection=Yes"
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT dbo.RMS_DailySales.ItemNumber, dbo.ItemMaster.ItemDescription AS Description, dbo.ItemMaster.ItemClassDescription AS Department, " & Chr(13) & "" & Chr(10) & " dbo.ItemMaster.ItemCategory1 AS Cate" _
, _
"gory, dbo.ItemMaster.CurrentCost AS Cost, dbo.RMS_DailySales.StoreCode AS [Store ID], dbo.RMS_DailySales.QtySold, " & Chr(13) & "" & Chr(10) & " dbo.RMS_DailySales.SoldPrice" & Chr(13) & "" & Chr(10) & "FROM dbo.RMS_DailySales" _
, _
" INNER JOIN" & Chr(13) & "" & Chr(10) & " dbo.ItemMaster ON dbo.RMS_DailySales.ItemNumber = dbo.ItemMaster.ItemNumber" & Chr(13) & "" & Chr(10) & "WHERE (dbo.RMS_DailySales.TransactionDate BETWEEN CONVERT(DATETIME, " _
, _
"" & Range("calcs!$E$25") & ", 102) AND CONVERT(DATETIME, " & Range("calcs!$E$26") & ", " & Chr(13) & "" & Chr(10) & " 102))" & Chr(13) & "" & Chr(10) & "AND(dbo.RMS_DailySales.ItemNumber = '" & myvar01 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar02 & "')" & Chr(13) & "" & Chr(10) & "or (db" _
, _
"o.RMS_DailySales.ItemNumber = '" & myvar03 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar04 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar05 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar06 & "" _
, _
"')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar07 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar08 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar09 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber =" _
, _
" '" & myvar10 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar11 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar12 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar13 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales." _
, _
"ItemNumber = '" & myvar14 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar15 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar16 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar17 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS" _
, _
"_DailySales.ItemNumber = '" & myvar18 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar19 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar20 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar21 & "')" & Chr(13) & "" _
, _
"" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar22 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar23 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar24 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar25 & "" _
, _
"')" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "GROUP BY dbo.RMS_DailySales.ItemNumber, dbo.ItemMaster.ItemDescription, dbo.RMS_DailySales.StoreCode, dbo.RMS_DailySales.QtySold, " & Chr(13) & "" & Chr(10) & " dbo.ItemMaster.ItemClassDescrip" _
, _
"tion, dbo.ItemMaster.CurrentCost, dbo.ItemMaster.ItemCategory1, dbo.RMS_DailySales.SoldPrice" _
)
.CreatePivotTable TableDestination:= _
"'[" & Range("workbookname") & "]ToDate'!R3C1", TableName:="ToDate", _
DefaultVersion:=xlPivotTableVersion10
End With
In this I am simply trying to save this SQL script into a macro so I can allow the user to alter the date ranges before its run.
StartDate = Range("calcs!B1")
EndDate = Range("calcs!B2")
SELECT
dbo.RMS_DailySales.ItemNumber, dbo.ItemMaster.ItemDescription AS Description,
dbo.ItemMaster.ItemClassDescription AS Department, dbo.ItemMaster.ItemCategory1 AS Category, dbo.ItemMaster.CurrentCost AS Cost, dbo.RMS_DailySales.StoreCode AS [Store ID], dbo.RMS_DailySales.QtySold, dbo.RMS_DailySales.SoldPrice FROM dbo.RMS_DailySales
INNER JOIN dbo.ItemMaster ON dbo.RMS_DailySales.ItemNumber = dbo.ItemMaster.ItemNumber
WHERE dbo.RMS_DailySales.TransactionDate BETWEEN
CONVERT(DATETIME, " & StartDate & ", 102) AND
CONVERT(DATETIME, " & EndDate & ", 102) AND
dbo.RMS_DailySales.ItemNumber = '9315626006338' or
dbo.RMS_DailySales.ItemNumber = '9315626006352' or
dbo.RMS_DailySales.ItemNumber = '9315626006345' or
dbo.RMS_DailySales.ItemNumber = '9315626006314' or
dbo.RMS_DailySales.ItemNumber = '9315626009896' or
dbo.RMS_DailySales.ItemNumber = '9315626006642' or
dbo.RMS_DailySales.ItemNumber = '9315626009421' or
dbo.RMS_DailySales.ItemNumber = '9315626009438' or
dbo.RMS_DailySales.ItemNumber = '9315626011509' or
dbo.RMS_DailySales.ItemNumber = '9315626006369' or
dbo.RMS_DailySales.ItemNumber = '9315626006307'
GROUP BY
dbo.RMS_DailySales.ItemNumber, dbo.ItemMaster.ItemDescription,
dbo.RMS_DailySales.StoreCode, dbo.RMS_DailySales.QtySold,
dbo.ItemMaster.ItemClassDescription, dbo.ItemMaster.CurrentCost,
dbo.ItemMaster.ItemCategory1, dbo.RMS_DailySales.SoldPrice
Thanks again
The company I work for is going though a growth spurt with regard to reporting requirments. I've recently been tailoring reports to suit as many as 30 new types of report. As our management team prefer using Excel, i've created many automated worksheets which run a various of SQL scripts in the background.
Currently im faced with the task of creating a worksheet in which staff would manually enter product barcodes (as many as 80 products) , for my script to pick up and collect sales data upon.
While I managed to successfully create a small version allowing the use 25 products, any more lines caused the macro to fail as there were too many characters in the unbroken line or something.
The data I collect is captured from many tables and are aften quite long in length. I normally simply record the macro while i connect to ODBC and edit the query in MS Query. It won't even record the full query. I've tried writing the code manually with the correct syntax but it stops me at a certain point because the script again contains too many characters.
I'd like to know...
1) is there an easier way connect to ODBC and run a specific query regardless of its size?
2) is there a better way to collect variable data (like my 80 products) from a range in excel.
I would so greatly appreciate assistance with this issue. It's really doing my head in now.
Thank you for all your great work. This site helps many of us keep our jobs and sometimes look really clever to boot.
example scripts below:
In this (recorded) macro i am collecting barcodes for randomly entered products...
myvar01 = Range("Promo_Items!A2")
myvar02 = Range("Promo_Items!A3")
myvar03 = Range("Promo_Items!A4")
myvar04 = Range("Promo_Items!A5")
myvar05 = Range("Promo_Items!A6")
myvar06 = Range("Promo_Items!A7")
myvar07 = Range("Promo_Items!A8")
myvar08 = Range("Promo_Items!A9")
myvar09 = Range("Promo_Items!A10")
myvar10 = Range("Promo_Items!A11")
myvar11 = Range("Promo_Items!A12")
myvar12 = Range("Promo_Items!A13")
myvar13 = Range("Promo_Items!A14")
myvar14 = Range("Promo_Items!A15")
myvar15 = Range("Promo_Items!A16")
myvar16 = Range("Promo_Items!A17")
myvar17 = Range("Promo_Items!A18")
myvar18 = Range("Promo_Items!A19")
myvar19 = Range("Promo_Items!A20")
myvar20 = Range("Promo_Items!A21")
myvar21 = Range("Promo_Items!A22")
myvar22 = Range("Promo_Items!A23")
myvar23 = Range("Promo_Items!A24")
myvar24 = Range("Promo_Items!A25")
myvar25 = Range("Promo_Items!A26")
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = _
"ODBC;DSN=WHSE;Description=RMS Sales;APP=Microsoft Office 2003;WSID=ITUSER;DATABASE=WHSE;Trusted_Connection=Yes"
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT dbo.RMS_DailySales.ItemNumber, dbo.ItemMaster.ItemDescription AS Description, dbo.ItemMaster.ItemClassDescription AS Department, " & Chr(13) & "" & Chr(10) & " dbo.ItemMaster.ItemCategory1 AS Cate" _
, _
"gory, dbo.ItemMaster.CurrentCost AS Cost, dbo.RMS_DailySales.StoreCode AS [Store ID], dbo.RMS_DailySales.QtySold, " & Chr(13) & "" & Chr(10) & " dbo.RMS_DailySales.SoldPrice" & Chr(13) & "" & Chr(10) & "FROM dbo.RMS_DailySales" _
, _
" INNER JOIN" & Chr(13) & "" & Chr(10) & " dbo.ItemMaster ON dbo.RMS_DailySales.ItemNumber = dbo.ItemMaster.ItemNumber" & Chr(13) & "" & Chr(10) & "WHERE (dbo.RMS_DailySales.TransactionDate BETWEEN CONVERT(DATETIME, " _
, _
"" & Range("calcs!$E$25") & ", 102) AND CONVERT(DATETIME, " & Range("calcs!$E$26") & ", " & Chr(13) & "" & Chr(10) & " 102))" & Chr(13) & "" & Chr(10) & "AND(dbo.RMS_DailySales.ItemNumber = '" & myvar01 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar02 & "')" & Chr(13) & "" & Chr(10) & "or (db" _
, _
"o.RMS_DailySales.ItemNumber = '" & myvar03 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar04 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar05 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar06 & "" _
, _
"')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar07 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar08 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar09 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber =" _
, _
" '" & myvar10 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar11 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar12 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar13 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales." _
, _
"ItemNumber = '" & myvar14 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar15 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar16 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar17 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS" _
, _
"_DailySales.ItemNumber = '" & myvar18 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar19 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar20 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar21 & "')" & Chr(13) & "" _
, _
"" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar22 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar23 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar24 & "')" & Chr(13) & "" & Chr(10) & "or (dbo.RMS_DailySales.ItemNumber = '" & myvar25 & "" _
, _
"')" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "GROUP BY dbo.RMS_DailySales.ItemNumber, dbo.ItemMaster.ItemDescription, dbo.RMS_DailySales.StoreCode, dbo.RMS_DailySales.QtySold, " & Chr(13) & "" & Chr(10) & " dbo.ItemMaster.ItemClassDescrip" _
, _
"tion, dbo.ItemMaster.CurrentCost, dbo.ItemMaster.ItemCategory1, dbo.RMS_DailySales.SoldPrice" _
)
.CreatePivotTable TableDestination:= _
"'[" & Range("workbookname") & "]ToDate'!R3C1", TableName:="ToDate", _
DefaultVersion:=xlPivotTableVersion10
End With
In this I am simply trying to save this SQL script into a macro so I can allow the user to alter the date ranges before its run.
StartDate = Range("calcs!B1")
EndDate = Range("calcs!B2")
SELECT
dbo.RMS_DailySales.ItemNumber, dbo.ItemMaster.ItemDescription AS Description,
dbo.ItemMaster.ItemClassDescription AS Department, dbo.ItemMaster.ItemCategory1 AS Category, dbo.ItemMaster.CurrentCost AS Cost, dbo.RMS_DailySales.StoreCode AS [Store ID], dbo.RMS_DailySales.QtySold, dbo.RMS_DailySales.SoldPrice FROM dbo.RMS_DailySales
INNER JOIN dbo.ItemMaster ON dbo.RMS_DailySales.ItemNumber = dbo.ItemMaster.ItemNumber
WHERE dbo.RMS_DailySales.TransactionDate BETWEEN
CONVERT(DATETIME, " & StartDate & ", 102) AND
CONVERT(DATETIME, " & EndDate & ", 102) AND
dbo.RMS_DailySales.ItemNumber = '9315626006338' or
dbo.RMS_DailySales.ItemNumber = '9315626006352' or
dbo.RMS_DailySales.ItemNumber = '9315626006345' or
dbo.RMS_DailySales.ItemNumber = '9315626006314' or
dbo.RMS_DailySales.ItemNumber = '9315626009896' or
dbo.RMS_DailySales.ItemNumber = '9315626006642' or
dbo.RMS_DailySales.ItemNumber = '9315626009421' or
dbo.RMS_DailySales.ItemNumber = '9315626009438' or
dbo.RMS_DailySales.ItemNumber = '9315626011509' or
dbo.RMS_DailySales.ItemNumber = '9315626006369' or
dbo.RMS_DailySales.ItemNumber = '9315626006307'
GROUP BY
dbo.RMS_DailySales.ItemNumber, dbo.ItemMaster.ItemDescription,
dbo.RMS_DailySales.StoreCode, dbo.RMS_DailySales.QtySold,
dbo.ItemMaster.ItemClassDescription, dbo.ItemMaster.CurrentCost,
dbo.ItemMaster.ItemCategory1, dbo.RMS_DailySales.SoldPrice
Thanks again
