Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

From 32 to 64 bits >< 2

Status
Not open for further replies.

ItIsHardToProgram

Technical User
Mar 28, 2006
946
CA
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.

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.
 
Don't have time to read through everything you posted but three things come to mind.

1 - Always code to the lesser machine

2 - break out your code into more subs

3 - use lots more comments to describe what your code is doing.
 
Yes HughLerwill, this is actualy the article that I started working upon, but I don't know if it is because I am trying to resist change, but I don't quite grap how I should start to apply it. They talk about conditional, "if VB7" which is probably what I should start using, but I don't know which statements, beside long declarations, need a new "makeup" for 64bits.

Maybe I am brain lazy. I will try to figure out eventualy ><

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top