This is my technical area for troubleshooting and learning new programming skills and much more. Here, you will find answers on a wide range of technologies such as AI, Machine Learning, OpenAI, Databricks, ASP.NET, C#, Python, Microsoft Access, MySQL, Amazon Web Services, SQL Server, PL/SQL, JD Edwards, SAS, Salesforce, APIs, MVC, and many others. Please feel free to visit and join the discussion!
Wednesday, April 3, 2013
Saturday, March 30, 2013
Wednesday, March 13, 2013
How to select cells/ranges by using Visual Basic procedures in Excel
ActiveSheet.Cells(5, 4).Select
http://support.microsoft.com/kb/291308ActiveSheet.Range("D5").Select
Excel VBA ComboBox Populate dynamic value
Tuesday, March 5, 2013
Write A Report in Excel using VBA - SQL-server
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnStart As Range
Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=xxxxx;Data Source=devDB;User ID=myuser;Password=mypass; Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=myPC;Use Encryption for Data=False;Tag with column collation when possible=False"
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets(5)
With wsSheet
Set rnStart = .Range("A7")
End With
'Report Name
Cells(1, 10).Value = "Interco Report"
stSQL = "SELECT top 100 * FROM tbl_intercoRep"
'stSQL = "SP_Int_DeleteThis"
' stSQL = "SP_IntercoReport"
Set cnt = New ADODB.Connection
With cnt
.CursorLocation = adUseClient
.Open stADO
.CommandTimeout = 0
Set rst = .Execute(stSQL)
End With
'Worksheets("sheet5").Activate
Dim ii As Integer
For ii = 0 To rst.Fields.Count - 1
Cells(6, ii + 1).Value = rst.Fields(ii).Name
Next ii
With Range("A1:ZZ1").Font
.Bold = True
.Size = 15
End With
With Range("A6:ZZ6").Font
.Bold = True
End With
'Here we add the Recordset to the sheet from A1
rnStart.CopyFromRecordset rst
'Cleaning up.
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub
Saturday, March 2, 2013
Building a Word Document Using SQL Server Data
Thursday, February 28, 2013
Display Sql-server Data into Excel Using VBA
-->
Tuesday, February 26, 2013
Parent Child Recursive Relation SQL-Server 2008 JDE
drop table #temp2
select yaan8,a.ABALPH ChildName, yaanpa,b.ABALPH ParentName into #temp2 from proddta.F060116
left outer join PRODDTA.F0101 a on yaan8=a.aban8
left outer join PRODDTA.F0101 b on yaanpa=b.aban8
where yapast in('1','0','6') order by 2
go
WITH Recursive_CTE AS (
SELECT
child.YAAN8,
CAST(child.ChildName as varchar(100)) ChildName,
child.yaanpa yaanpa,
CAST(NULL as varchar(100)) ParentUnit,
CAST('>> ' as varchar(100)) LVL,
CAST(child.YAAN8 as varchar(100)) Hierarchy,
1 AS RecursionLevel
FROM #temp2 child
WHERE YAAN8 = 123456
UNION ALL
SELECT
child.YAAN8,
CAST(LVL + child.ChildName as varchar(100)) AS ChildName,
child.yaanpa,
parent.ChildName ParentUnit,
CAST('>> ' + LVL as varchar(100)) AS LVL,
CAST(Hierarchy + ':' + CAST(child.YAAN8 as varchar(100)) as varchar(100)) Hierarchy,
RecursionLevel + 1 AS RecursionLevel
FROM Recursive_CTE parent
INNER JOIN #temp2 child ON child.yaanpa = parent.YAAN8
)
SELECT * FROM Recursive_CTE ORDER BY Hierarchy
Thursday, February 21, 2013
Writing Word from Database or Excel
Writing Word from Database
Best way to do this using Access Database, do following:-
1. Create a Table in access database and define all of the related data for mail merge.
2. Open this table and click on External Data Word Merge.
3. Word document will open, Select second option –> Create a new document and then link the data to it.
4. Type your letter and if you want to attach a field you can click at insert merge field and select your field.
5. After this you can check the preview.
6. Click at Finish & merge, once you done.
I think you can use excel by attaching it to access database.