Add formular to VBA code, export to Excel Table

[sebble1984]

Hi Guys,
I have a report exporting to excel using late binding techniques.
When exported into excel i have numbers for 1,2,3,4 tblPreSiteSurveys.PreSiteSurveyStopTheClockReason entitie and I am trying to either change the numbers here casting from int to string
1 = a
2 = b
looping through the record set, Or i have a blank field in my SQL for the column "P" and adding the formular to that column, but it only goes into the first row of the record set, which is a expandable table.



On Error GoTo Command29_Click_Err
'Utilergy Master Update report
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim UserDate As Date
Dim StartDate As Date
Dim EndDate As Date
Dim ForStartDate As Date
Dim ForEndDate As Date
Dim rsCount As Integer
Dim strFormular As String
If IsNull(Me.TxTStartDate.Value) Or IsNull(Me.TxTEndDate.Value) Then

MsgBox "Please Enter Start and End Dates for the Report to be Processed", vbInformation, "Utilergy"

Else
StartDate = Me.TxTStartDate.Value
EndDate = Me.TxTEndDate.Value
ForStartDate = Format(StartDate, "DD/MM/YYYY")
ForEndDate = Format(EndDate, "DD/MM/YYYY")


Dim strSQL As String
strSQL = "SELECT tblProjects.ProjectDateModfied, tblProjects.ProjectNumber, tblWorks.WorkNumber, tblOrders.OrderNumber, autotblProjectStatus.ProjectStatus," & vbCrLf
strSQL = strSQL & "tblWorks.WorkAddressNameNumber & ' ' & tblWorks.WorkAddressStreet & ' ' & tblWorks.WorkAddressTown & ' ' & tblWorks.WorkAddressPostcode, tblProjects.ProjectRecievedDate," & vbCrLf
strSQL = strSQL & "tblPreSiteSurveys.PreSiteSurveyDate, tblProjects.ProjectProvisionalStartDate, tblProjects.ProjectCompletionDate, tblProjects.ProjectActualStartDate," & vbCrLf
strSQL = strSQL & "tblProjects.ProjectActualCompletionDate, tblPreSiteSurveys.PreSiteSurveyStopTheClockDate, tblPreSiteSurveys.PreSiteSurveyStopTheClockCode, tblPreSiteSurveys.PreSiteSurveyStopTheClockReason, ' ' , " & vbCrLf
strSQL = strSQL & "tblPreSiteSurveys.PreSiteSurveyStopTheClockComment, tblWorks.WorkEnergisationDate, tblAsBuiltDetails.AsBuiltDetailsDateSubmittedToENW, tblPreSiteSurveys.PreSiteSurveyContactName," & vbCrLf
strSQL = strSQL & "tblPreSiteSurveys.PreSiteSurveyContactNumber, tblAsBuiltDetails.AsBuiltDetailsDateJointed" & vbCrLf
strSQL = strSQL & "FROM (((tblPreSiteSurveys INNER JOIN (autotblProjectStatus INNER JOIN tblProjects ON autotblProjectStatus.[ProjectStatusID] = tblProjects.[ProjectStatus])" & vbCrLf
strSQL = strSQL & "ON tblPreSiteSurveys.[PreSiteSurveyID] = tblProjects.[FKPreSiteSurveyID]) INNER JOIN tblWorks ON tblProjects.[ProjectID] = tblWorks.[FKProjectID])" & vbCrLf
strSQL = strSQL & "LEFT OUTER JOIN tblAsBuiltDetails ON tblWorks.[WorkID] = tblAsBuiltDetails.[FKWorkID]) INNER JOIN tblOrders ON tblWorks.WorkID = tblOrders.FKWorkID" & vbCrLf
strSQL = strSQL & "WHERE tblProjects.ProjectDateModfied between #" & ForStartDate & "# AND #" & ForEndDate & "# ;"
'counts the number of records for Cell F7 of thhe spreadsheet
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
rsCount = rs.RecordCount


'Start a new workbook in Excel
Dim oApp As Object
Dim oBook As Object

Set oApp = CreateObject("excel.Application")

oApp.Workbooks.Open ("MasterUpdate.xlsm")
oApp.Visible = True
Set oBook = oApp.ActiveWorkbook

Set oSheet = oBook.Worksheets(1)
ForStartDate = Format(StartDate, "DD/MM/YYYY")
ForEndDate = Format(EndDate, "DD/MM/YYYY")
'Add the field names in row 1
' Dim i As Integer
' Dim iNumCols As Integer
' iNumCols = rs.Fields.Count
' For i = 1 To iNumCols
' oSheet.Cells(2, i).Value = rs.Fields(i - 1).Name
' Next

strFormular = "=IF(O3=1,""Weather"","" "")"
'Add the data starting at cell A2
oSheet.Range("A3").CopyFromRecordset rs
oSheet.Range("P3").Formula = strFormular
' oSheet.Range("E13").Value = ForStartDate
' oSheet.Range("E14").Value = ForEndDate


oApp.Visible = True
oApp.UserControl = True

'Close the Database and Recordset
rs.Close
Command29_Click_Exit:
Exit Sub
Command29_Click_Err:

MsgBox Error$
Resume Command29_Click_Exit
End If

Any help or advice would be great thanks. Cheers :banghead:

 

[namliam]

Please use code tags when you post code (see my sig for more detials)

So you are trying to reformat your column PreSiteSurveyStopTheClockReason
Why do you report other values than stored in your DB, if need by cant that data be fedged from within the db?

What is exactly that you are trying to do, I have a feeling you are looking for CHR(value + 64) or chr(value + 96), converting the 1 into A or a respectively

 

[namliam]

Some design issues are already appearent from just this piece of code...
Command29, dont leave default names in any objects, this is a maintenance nightmare

You have a StopTheClockCode and StopTheClockReason, but appearently neither are clear enough and you still need to change them?

You should make sure your ForStartDate and ForEndDate go into your SQL as US formatting, Format(yourdate, "MM/DD/YYYY") instead of the current DD/MM/YYYY, which WILL cause you issues

Proper indentation and SQL formatting to make your code (more) readable, will make your code (more) maintainable.

Probably more to be found, however on a positive note, atleast you are working with a proper naming convention

 

[sebble1984]

I am exporting to excel and currently have numbers in PreSiteSurveyStopTheClockReason column, which mean something.

But when i export them to Excel i would like the numbers to change to text,
1 = weather
2 = customer
3 = new
4 = old etc.

I am not sure if it is best to cast to strings from ints and loop through the recordset in the VBA prior to exporting to excel, or add another column in excel and have a formula there.

Either or, i am unsure or either solution.
I thought the code looked poor, with no highlighting
Thanks Seb,

 

[namliam]

Best solution would be to to redesign the entire db (but that is probably way overdone)

Best way to make an additional table in Access that has your conversion in it.
Then join this table in the query prior to export and your done.

 

[sebble1984]

Sorry, Whats makes you think the best way would be to re-design the database, from one piece of code?

 

[namliam]

For one thing your current requirement, another your query, another the comments I posted above... But like I said at this point that is probably way overdone.... But I am positive there could be some gains from redesign....

For now adding the additional table would seem to be enough

 

[sebble1984]

How do i go about using the CHR(value + 64) or chr(value + 96) through the whole record set please?
Thanks

 

[namliam]

By calculating a calculated value in a query....

i.e. add just before the "From" in the query:
strSQL = strSQL & ", Chr(YourField + 64) "

However you do not want to translate 1 into an A, like you suggested in the OP, you want to translate 1 into Weather, for this you cannot use CHR.
Instead, like I said in my previous post, I suggest you make a table with 2 columns
id description
1 weather
2 customer
3 new

Join it in the query and display the description you want instead of the id

 

[sebble1984]

Hi, I updated the SQL.
Thanks for your advice: -

   strSQL = " SELECT tblProjects.ProjectDateModfied, tblProjects.ProjectNumber, tblWorks.WorkNumber, tblOrders.OrderNumber, autotblProjectStatus.ProjectStatus, " & vbCrLf
strSQL = strSQL & "tblWorks.WorkAddressNameNumber & ' ' & tblWorks.WorkAddressStreet & ' ' & tblWorks.WorkAddressTown & ' ' & tblWorks.WorkAddressPostcode AS Expr1, " & vbCrLf
strSQL = strSQL & "tblProjects.ProjectRecievedDate, tblPreSiteSurveys.PreSiteSurveyDate, tblProjects.ProjectProvisionalStartDate, tblProjects.ProjectCompletionDate, " & vbCrLf
strSQL = strSQL & "tblProjects.ProjectActualStartDate, tblProjects.ProjectActualCompletionDate, tblPreSiteSurveys.PreSiteSurveyStopTheClockDate, " & vbCrLf
strSQL = strSQL & "tblPreSiteSurveys.PreSiteSurveyStopTheClockCode, autotblStopTheClockReason.StopTheClockReason, tblPreSiteSurveys.PreSiteSurveyStopTheClockComment, " & vbCrLf
strSQL = strSQL & "tblWorks.WorkEnergisationDate, tblAsBuiltDetails.AsBuiltDetailsDateSubmittedToENW, tblPreSiteSurveys.PreSiteSurveyContactName, tblPreSiteSurveys.PreSiteSurveyContactNumber, tblAsBuiltDetails.AsBuiltDetailsDateJointed" & vbCrLf
strSQL = strSQL & "FROM autotblStopTheClockReason " & vbCrLf
strSQL = strSQL & "RIGHT JOIN ((((tblPreSiteSurveys INNER JOIN (autotblProjectStatus " & vbCrLf
strSQL = strSQL & "INNER JOIN tblProjects ON autotblProjectStatus.[ProjectStatusID] = tblProjects.[ProjectStatus]) " & vbCrLf
strSQL = strSQL & "ON tblPreSiteSurveys.[PreSiteSurveyID] = tblProjects.[FKPreSiteSurveyID]) " & vbCrLf
strSQL = strSQL & "INNER JOIN tblWorks ON tblProjects.[ProjectID] = tblWorks.[FKProjectID]) " & vbCrLf
strSQL = strSQL & "LEFT JOIN tblAsBuiltDetails ON tblWorks.[WorkID] = tblAsBuiltDetails.[FKWorkID]) " & vbCrLf
strSQL = strSQL & "INNER JOIN tblOrders ON tblWorks.WorkID = tblOrders.FKWorkID) ON autotblStopTheClockReason.StopTheClockReasonID = tblPreSiteSurveys.PreSiteSurveyStopTheClockReason " & vbCrLf
strSQL = strSQL & "WHERE tblProjects.ProjectDateModfied between  #" & ForStartDate & "# AND #" & ForEndDate & "# ;"

 

[namliam]

Is it really that hard to format SQL in VBA? Seems like no one cares anymore

*shakes head* / walks away

 

[sebble1984]

obviously not

 

[namliam]

obviously not

Obviously it is

*shakes head* / walks away