ItIsHardToProgram
Technical User
Hey,
I don't want to take a whole lot of your time, I am simply looking for a good article on how to design or tweak a code for it to work on 64bits and 32 bits machines.
To put you guys into context I have designed about a year and a half ago a sort of database for timesheet / projects, in order to have rough estimates for following budgets.
The way this work is quite simple, there is a access recordset in which the excel timesheet records data, through DAO or ADO in VBA, according to what is entered on the excel sheet.
Here is the challenge:
The excel sheets are run and passed onto the server (a simple save as command) with 64 bit machines, the excel sheets are then opened on a 32 bit OR 64 bit machine, and written on the access recordset through DAO.
I use ini files in order to pass the file informations (path, etc...) I think this is the major 64/32 bits issue, but I am not sure that is the only one.
I have a rough idea on how to do this, but it would take me approximatly 20 hours and would not be perfect.
So if you could point me to a open source project or an article that you believe would help me achieve this in reasonable time, please feel free to do so, let me give you the code that I use, in integrality, if you even want to take a look to understand better, thank you.
*N.B.*No need to explain to me the multiple problems of having 32/64 bits machine, and the multiple problems of multiple machine writing on the recordset and having multiple machine process the timesheets. I have a good grasp of these problems, but I am not in control of the decisions, I simply execute what is asked of me, and do my best to cope.
"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
I don't want to take a whole lot of your time, I am simply looking for a good article on how to design or tweak a code for it to work on 64bits and 32 bits machines.
To put you guys into context I have designed about a year and a half ago a sort of database for timesheet / projects, in order to have rough estimates for following budgets.
The way this work is quite simple, there is a access recordset in which the excel timesheet records data, through DAO or ADO in VBA, according to what is entered on the excel sheet.
Here is the challenge:
The excel sheets are run and passed onto the server (a simple save as command) with 64 bit machines, the excel sheets are then opened on a 32 bit OR 64 bit machine, and written on the access recordset through DAO.
I use ini files in order to pass the file informations (path, etc...) I think this is the major 64/32 bits issue, but I am not sure that is the only one.
I have a rough idea on how to do this, but it would take me approximatly 20 hours and would not be perfect.
So if you could point me to a open source project or an article that you believe would help me achieve this in reasonable time, please feel free to do so, let me give you the code that I use, in integrality, if you even want to take a look to understand better, thank you.
Code:
Option Explicit
' ne jamais changer le nom du TAB ou venir le changer ici aussi
Public Const sSheetNM As String = "TimeSheet"
'Variables concerning the database
Public sMDBFileDir As String
Public sMDBFileName As String
Private dbtmp As DAO.Database
Private Const MAX_USERNAME As Long = 256
Private Const gintMAX_SIZE% = 255 'Maximum buffer size
Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal lSize As Long, ByVal lpFileName As String) As Long
Private Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long
Private Declare Function GetUserName Lib "advapi32" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public Function TransfertFeuilleTemps()
BackupTimeSheet (Worksheets(sSheetNM).Range("F11").Value)
End Function
Public Function AddFeuilleTemps()
Dim iMaxLine As Integer
Dim i As Integer
Dim iTemp As Integer
Dim RstTimeSheet As DAO.Recordset
Dim bDBwritten As Boolean
Dim j As Integer
Dim sPath As String
Worksheets(sSheetNM).Activate
Worksheets(sSheetNM).Range("A1").Activate
iMaxLine = ActiveCell.End(xlToRight).Column
'dVal = Worksheets("Tableau").Range("F" & i).Value
'iTemp = Worksheets(sSheetNM).Range("C16").Value
For j = 60 To 73
If Len(Worksheets(sSheetNM).Range("B" & j).Value) > 7 And Len(Worksheets(sSheetNM).Range("B" & j).Value) < 12 And Len(Worksheets(sSheetNM).Range("C" & j).Text) < 5 Then
MsgBox ("Vous devez entrer une meilleure description de vos projet R&D")
Exit Function
End If
Next j
i = 15
'Ouverture de la BD et du Rec.Set
Call sGetRS(RstTimeSheet, "tblTimeSheet")
'Loop sur les 16 ligne, 2 part 2 car la 2iem c pour le R&D du meme projet
Do While i <= 73
'valid si il y a des hers dans la col K, si non skip
If Worksheets(sSheetNM).Range("L" & i).Value + Worksheets(sSheetNM).Range("M" & i).Value > 0 And i < 58 Then
bDBwritten = True
'iTemp = Worksheets(sSheetNM).Range("C" & i).Value
With RstTimeSheet
.AddNew
!IdProjet = Worksheets(sSheetNM).Range("B" & i).Value
'!nom = Worksheets(sSheetNM).Range("F9").Value
!idemployer = Worksheets(sSheetNM).Range("F10").Value
!semaine = Worksheets(sSheetNM).Range("F11").Value
If Worksheets(sSheetNM).Range("C" & i).Value <> "" Then !Description = Worksheets(sSheetNM).Range("C" & i).Value
If Worksheets(sSheetNM).Range("C" & i).Value <> "" Then !DescriptionRD = Worksheets(sSheetNM).Range("C" & i).Value
!Lundi = IIf((Worksheets(sSheetNM).Range("D" & i).Value) > 0, Worksheets(sSheetNM).Range("D" & i).Value, 0)
!mardi = IIf((Worksheets(sSheetNM).Range("E" & i).Value) > 0, Worksheets(sSheetNM).Range("E" & i).Value, 0)
!mercredi = IIf((Worksheets(sSheetNM).Range("F" & i).Value) > 0, Worksheets(sSheetNM).Range("F" & i).Value, 0)
!jeudi = IIf((Worksheets(sSheetNM).Range("G" & i).Value) > 0, Worksheets(sSheetNM).Range("G" & i).Value, 0)
!vendredi = IIf((Worksheets(sSheetNM).Range("H" & i).Value) > 0, Worksheets(sSheetNM).Range("H" & i).Value, 0)
!samedi = IIf((Worksheets(sSheetNM).Range("I" & i).Value) > 0, Worksheets(sSheetNM).Range("I" & i).Value, 0)
!dimanche = IIf((Worksheets(sSheetNM).Range("J" & i).Value) > 0, Worksheets(sSheetNM).Range("J" & i).Value, 0)
!LundiRD = IIf((Worksheets(sSheetNM).Range("D" & i + 1).Value) > 0, Worksheets(sSheetNM).Range("D" & i + 1).Value, 0)
!mardiRD = IIf((Worksheets(sSheetNM).Range("E" & i + 1).Value) > 0, Worksheets(sSheetNM).Range("E" & i + 1).Value, 0)
!mercrediRD = IIf((Worksheets(sSheetNM).Range("F" & i + 1).Value) > 0, Worksheets(sSheetNM).Range("F" & i + 1).Value, 0)
!jeudiRD = IIf((Worksheets(sSheetNM).Range("G" & i + 1).Value) > 0, Worksheets(sSheetNM).Range("G" & i + 1).Value, 0)
!vendrediRD = IIf((Worksheets(sSheetNM).Range("H" & i + 1).Value) > 0, Worksheets(sSheetNM).Range("H" & i + 1).Value, 0)
!samediRD = IIf((Worksheets(sSheetNM).Range("I" & i + 1).Value) > 0, Worksheets(sSheetNM).Range("I" & i + 1).Value, 0)
!dimancheRD = IIf((Worksheets(sSheetNM).Range("J" & i + 1).Value) > 0, Worksheets(sSheetNM).Range("J" & i + 1).Value, 0)
!HresReg = IIf((Worksheets(sSheetNM).Range("F39").Value) > 0, Worksheets(sSheetNM).Range("F39").Value, 0)
!HresRD = IIf((Worksheets(sSheetNM).Range("H39").Value) > 0, Worksheets(sSheetNM).Range("H39").Value, 0)
.Update
End With
ElseIf Worksheets(sSheetNM).Range("L" & i).Value > 0 And i > 58 Then
bDBwritten = True
'iTemp = Worksheets(sSheetNM).Range("C" & i).Value
With RstTimeSheet
.AddNew
!IdProjet = Worksheets(sSheetNM).Range("B" & i).Value
'!nom = Worksheets(sSheetNM).Range("F9").Value
!idemployer = Worksheets(sSheetNM).Range("F10").Value
!semaine = Worksheets(sSheetNM).Range("F11").Value
If Worksheets(sSheetNM).Range("C" & i).Value <> "" Then !Description = Worksheets(sSheetNM).Range("C" & i).Value
If Worksheets(sSheetNM).Range("C" & i).Value <> "" Then !DescriptionRD = Worksheets(sSheetNM).Range("C" & i).Value
!LundiRD = IIf((Worksheets(sSheetNM).Range("D" & i).Value) > 0, Worksheets(sSheetNM).Range("D" & i).Value, 0)
!mardiRD = IIf((Worksheets(sSheetNM).Range("E" & i).Value) > 0, Worksheets(sSheetNM).Range("E" & i).Value, 0)
!mercrediRD = IIf((Worksheets(sSheetNM).Range("F" & i).Value) > 0, Worksheets(sSheetNM).Range("F" & i).Value, 0)
!jeudiRD = IIf((Worksheets(sSheetNM).Range("G" & i).Value) > 0, Worksheets(sSheetNM).Range("G" & i).Value, 0)
!vendrediRD = IIf((Worksheets(sSheetNM).Range("H" & i).Value) > 0, Worksheets(sSheetNM).Range("H" & i).Value, 0)
!samediRD = IIf((Worksheets(sSheetNM).Range("I" & i).Value) > 0, Worksheets(sSheetNM).Range("I" & i).Value, 0)
!dimancheRD = IIf((Worksheets(sSheetNM).Range("J" & i).Value) > 0, Worksheets(sSheetNM).Range("J" & i).Value, 0)
.Update
End With
i = i - 1
End If
i = i + 2
Loop
If bDBwritten = True Then
RstTimeSheet.Close
Set RstTimeSheet = Nothing
bDBwritten = False
Else
MsgBox ("Votre feuille de temps ne contient aucune donnée numérique")
End If
sPath = "Z:\General\General MRA\MasterMDB\"
ActiveWorkbook.SaveAs Filename:=sPath & "Valide\" & ThisWorkbook.Name & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
End Function
'******************************************************************************
'* S.Guerin 10-03-2009
'*
'******************************************************************************
Public Sub BackupTimeSheet(ByVal sSemaine As String)
Dim FSOobj As Object
Dim sPath As String
Dim sNmFile As String
Dim SPLITsSemaine() As String
Set FSOobj = CreateObject("Scripting.FilesystemObject")
SPLITsSemaine = Split(sSemaine, "/")
If Len(SPLITsSemaine(0)) > 5 Then
Else
sSemaine = SPLITsSemaine(0) + "-" + SPLITsSemaine(1) + "-" + SPLITsSemaine(2)
End If
sPath = ReadIniFile(ActiveWorkbook.Path & "\MasterDB.ini", "Backup", "BKPFileDir")
sNmFile = ReadIniFile(ActiveWorkbook.Path & "\MasterDB.ini", "Backup", "NameBKFile")
If FSOobj.FolderExists(ActiveWorkbook.Path & "\FeuilleComplete") = False Then
FSOobj.CreateFolder ActiveWorkbook.Path & "\FeuilleComplete"
Else
'le dossier existe
End If
Set FSOobj = Nothing
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\FeuilleComplete\" & sNmFile & sSemaine & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.SaveAs Filename:=sPath & sNmFile & sSemaine & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
End Sub
'******************************************************************************
'* S.Guerin 10-03-2005
'*
'******************************************************************************
Public Sub sGetRS(RS As DAO.Recordset, sTableName As String)
Dim wks As DAO.Workspace
Dim X As Integer
If Not RS Is Nothing Then
RS.Close
Set RS = Nothing
End If
'If dbtmp Is Nothing Then
Set dbtmp = OpenDatabase(sMDBFileDir & sMDBFileName, False, False, ";PWD=bicyclette")
'End If
DoEvents
Set RS = dbtmp.OpenRecordset("select * from " & sTableName)
End Sub
'******************************************************************************
' FUNCTION: ReadIniFile
'
' Reads a value from the specified section/key of the
' specified .INI file
'
' IN: [strIniFile] - name of .INI file to read
' [strSection] - section where key is found
' [strKey] - name of key to get the value of
'
' Returns: non-zero terminated value of .INI file key
'*******************************************************************************
Public Function ReadIniFile(ByVal strIniFile As String, _
ByVal strsection As String, _
ByVal strKey As String) As String
'------------------------------------------------------------------------------
Dim strBuffer As String
Dim intPos As Integer
'
'If successful read of .INI file, strip any trailing
'zero returned i the Windows API GetPrivateProfileString
'
strBuffer = Space$(gintMAX_SIZE)
If GetPrivateProfileString(strsection, strKey, vbNullString, strBuffer, gintMAX_SIZE, strIniFile) > 0 Then
ReadIniFile = RTrim$(strBuffer)
ReadIniFile = Mid(ReadIniFile, 1, Len(ReadIniFile) - 1)
End If
End Function*N.B.*No need to explain to me the multiple problems of having 32/64 bits machine, and the multiple problems of multiple machine writing on the recordset and having multiple machine process the timesheets. I have a good grasp of these problems, but I am not in control of the decisions, I simply execute what is asked of me, and do my best to cope.
"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
