VBA excel:move results one column right every 1st day of the month

Posted: June 9, 2015 in Scripts
Tags: ,

You have to retrive data from database,results consist from two column,during october results are populated in columns A and B,and during november results must be retrived in colums C and D,and in december E and F respectively.Here is how to accomplish that:

First,be sure that macros are enabled (File-Options-Trust Center-Trust Center Settings-Macro Settings-Enable All macros

Insert new module and paste this code

(I am using Oracle OO40 driver to connect to the database,but your method may vary)

Sub auto_open()

Open “C:\Local Data\autowater\pass.txt” For Input As #1
Input #1, shorttext
Close #1
mystring = shorttext
Open “C:\Local Data\autowater\pass1.txt” For Input As #2
Input #2, shorttext
Close #2
mystring1 = shorttext

Dim objSession As Object
Dim objDataBase As Object

Dim v As Variant
Dim pom As String
If (Month(Date)) = 10 Then

v = v + 1
pom = “A:B”

End If

If (Month(Date)) = 11 And Day(Date) = 1 Then

v = v + 1
pom = “A:B”

ElseIf (Month(Date)) = 11 And Day(Date) <> 1 Then

v = v + 3
pom = “C:D”

End If

If (Month(Date)) = 12 And Day(Date) = 1 Then

v = v + 3
pom = “C:D”

ElseIf (Month(Date)) = 12 And Day(Date) <> 1 Then

v = v + 5
pom = “E:F”

End If

Set objSession = CreateObject(“OracleInProcServer.XOraSession”)

Set objDataBase = objSession.OpenDatabase(mystring, mystring1, 0&)

Dim sql As String

Sheets(“sheet2″).Select
Range(pom).Select
Selection.ClearContents

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

sql = sql & ” your SQL here”

Set oraDynaSet = objDataBase.DBCreateDynaset(sql, 0&)

If oraDynaSet.RecordCount > 0 Then
oraDynaSet.MoveFirst
For x = 0 To oraDynaSet.Fields.Count – 1
Cells(1, x + v) = oraDynaSet.Fields(x).Name

Next

For y = 0 To oraDynaSet.RecordCount – 1
For x = 0 To oraDynaSet.Fields.Count – 1
Cells(y + 2, x + v) = oraDynaSet.Fields(x).Value
Next
oraDynaSet.MoveNext
Next
End If
Application.Calculation = xlCalculationAutomatic
Set objSession = Nothing
Set objDataBase = Nothing
End Sub

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s