Compatibilité macro 32/64bits

Le 05/02/2018 à 09:57

Bonjour,

J'ai une macro qui me donne des erreurs de compilation à l'ouverture du fichier excel quand j'essai d'utiliser mon fichier sur des machines 64bits. Cette macro permet d'ouvrir puis d'importer un fichier .log directement dans un emplacement spécifier de mon fichier excel.

Code de la macro en question.

L'erreur de compilation viens de la ligne juste en dessous de "Option Explicit"

'Ouverture log fiche produit
Option Explicit

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
         "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
  lStructSize As Long
  hwndOwner As Long
  hInstance As Long
  lpstrFilter As String
  lpstrCustomFilter As String
  nMaxCustFilter As Long
  nFilterIndex As Long
  lpstrFile As String
  nMaxFile As Long
  lpstrFileTitle As String
  nMaxFileTitle As Long
  lpstrInitialDir As String
  lpstrTitle As String
  flags As Long
  nFileOffset As Integer
  nFileExtension As Integer
  lpstrDefExt As String
  lCustData As Long
  lpfnHook As Long
  lpTemplateName As String
End Type

Function GetFileName(sFilter As String, sInitialDir As String, sTitle As String) As String
  Dim OpenFile As OPENFILENAME, lReturn As Long

  With OpenFile
    .lStructSize = Len(OpenFile)
    .lpstrFilter = sFilter
    .nFilterIndex = 1
    .lpstrFile = String(257, 0)
    .nMaxFile = Len(OpenFile.lpstrFile) - 1
    .lpstrFileTitle = OpenFile.lpstrFile
    .nMaxFileTitle = OpenFile.nMaxFile
    .lpstrInitialDir = sInitialDir
    .lpstrTitle = sTitle
    .flags = 0
  End With
  lReturn = GetOpenFileName(OpenFile)

  If lReturn = 0 Then
    GetFileName = ""
  Else
     GetFileName = Trim(OpenFile.lpstrFile)
  End If

End Function

Sub Frame_Work_Import()
  Dim sPathFic As String, sFilter As String
  Dim sLocalPath As String

  sLocalPath = Application.ActiveWorkbook.Path & "\"

  sFilter = "Fichier d'export (*.log)" & Chr(0) & "*.log" & Chr(0)

  ' Donner le choix du fichier
 sPathFic = GetFileName(sFilter, sLocalPath, "Sélectionnez le fichier à ouvrir")
  If sPathFic = "" Then Exit Sub
  '
 With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & sPathFic, Destination:=Range("$W$2"))
    .Name = "copie"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1)
    .TextFileTrailingMinusNumbers = False
    .Refresh BackgroundQuery:=False
    Call Archivage
  End With
  End Sub

  'ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh

  Sub Archivage()
Dim SHsource As Worksheet, WBcible As Workbook

'feuille source
Set SHsource = ThisWorkbook.ActiveSheet

'classeur cible
On Error Resume Next
Set WBcible = Workbooks.Open(Filename:="autoimport-textfile\")

'copie de la feuille source à la fin du classeur cible
SHsource.Copy After:=WBcible.Sheets(Sheets.Count)

'renommer la feuille cible
WBcible.Sheets(Sheets.Count).Name = WBcible.Sheets(Sheets.Count).Cells(2, 21)

'fermeture et sauvegarde du classeur cible
WBcible.Close True

'libération de la mémoire
Set SHsource = Nothing
Set WBcible = Nothing
  ThisWorkbook.ActiveSheet.Select
  Range("A2").Select
  ActiveWorkbook.RefreshAll
End Sub

J'aimerai donc savoir si il était possible de rendre mon fichier/la macro compatible sur les système 64 comme 32.

Merci d'avance de vos réponses!

Le 05/02/2018 à 10:18

Bonjour,

C'est possible via ces instructions de compilation :

#If VBA7 Then
    Private Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
         "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As LongPtr
    Private Type OPENFILENAME
      lStructSize As LongPtr
      hwndOwner As LongPtr
      hInstance As LongPtr
      lpstrFilter As String
      lpstrCustomFilter As String
      nMaxCustFilter As LongPtr
      nFilterIndex As LongPtr
      lpstrFile As String
      nMaxFile As LongPtr
      lpstrFileTitle As String
      nMaxFileTitle As LongPtr
      lpstrInitialDir As String
      lpstrTitle As String
      flags As LongPtr
      nFileOffset As Integer
      nFileExtension As Integer
      lpstrDefExt As String
      lCustData As LongPtr
      lpfnHook As LongPtr
      lpTemplateName As String
    End Type
#Else
    Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
         "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
    Private Type OPENFILENAME
      lStructSize As Long
      hwndOwner As Long
      hInstance As Long
      lpstrFilter As String
      lpstrCustomFilter As String
      nMaxCustFilter As Long
      nFilterIndex As Long
      lpstrFile As String
      nMaxFile As Long
      lpstrFileTitle As String
      nMaxFileTitle As Long
      lpstrInitialDir As String
      lpstrTitle As String
      flags As Long
      nFileOffset As Integer
      nFileExtension As Integer
      lpstrDefExt As String
      lCustData As Long
      lpfnHook As Long
      lpTemplateName As String
    End Type
#End If
Le 05/02/2018 à 10:49

Bonjour,

Moi j'ai noté d'autres définitions : A tester si la défintion de Thev coince un peu et nous dire ce qui convient le mieux éventuellement....

Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
         "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Type OPENFILENAME
        lStructSize As Long
        hwndOwner As LongPtr
        hInstance As LongPtr
        lpstrFilter As String
        lpstrCustomFilter As String
        nMaxCustFilter As Long
        nFilterIndex As Long
        lpstrFile As String
        nMaxFile As Long
        lpstrFileTitle As String
        nMaxFileTitle As Long
        lpstrInitialDir As String
        lpstrTitle As String
        flags As Long
        nFileOffset As Integer
        nFileExtension As Integer
        lpstrDefExt As String
        lCustData As LongPtr
        lpfnHook As LongPtr
        lpTemplateName As String
'#if (_WIN32_WINNT >= 0x0500)
        pvReserved As LongPtr
        dwReserved As Long
        FlagsEx As Long
'#endif // (_WIN32_WINNT >= 0x0500)
End Type

A+

Le 05/02/2018 à 11:17

Bonjour Galopin,

Pour la compatibilité 64 bits, en principe toutes les variables définies en "Long" doivent être converties en "LongPtr" et en particulier celles figurant dans la définition de la fonction.

Cela m'étonnerait que ça fonctionne

Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
         "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Le 05/02/2018 à 14:08

Bonjour Thev,

Merci de ta réponses je te confirme que ta modification continue à marcher sur du 32 bits.

Dès que j'aurait accès à un ordinateur 64 bits je pourrait te confirmer si ça marche.

Rechercher des sujets similaires à "compatibilite macro 64bits"