Execution shell avec une macro

Bonjour,

J'ai un .bat qui a pour but de venir récupérer des fichiers pour les mettre dans la racine. et dont voici l'extrait:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
@ECHO OFF
 
:top
REM ******************************
REM * user et mot de passe       *
REM ******************************
set user=#######
set passwd=######
 
REM *********************************************
REM * les fichiers seront sur le disque suivant *
REM *********************************************
K:\#########
 
for /F "tokens=1-4 delims=/ " %%i in ('date /t') do set yyyymmdd=%%k%%j%%i
echo Date: %yyyymmdd%
 
 
set CRCO=10.103.249.21
 
 
get cftp
 
:cftp
ECHO open %CRCO% >  passFTP.txt
ECHO %USER%   	   >> passFTP.txt
ECHO %PASSWD%      >> passFTP.txt
ECHO GET 'CXSIAB00.JDPSN000.JDCJAAS1(0)' CRCO.txt >> passFTP.txt
ECHO close >> passFTP.txt
ECHO quit >> passFTP.txt
ftp -s:passFTP.txt
 
del passFTP.txt
 
ECHO.
REM ECHO Hasta la vista baby !
 
:end
:

Et dans mon fichier Excel qui est censé afficher les données des fichiers récupérés j'ai une macro de ce type:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
Sub MiseEnForme()
 
stArgs = "K:\##############"
retval = Shell(stArgs)
 
    Sheets("Modèle").Select
    Sheets("Modèle").Copy Before:=Sheets(1)
    Range("A17").Select
    Selection.Copy
    Sheets("Modèle (2)").Select
    Sheets("Modèle (2)").Name = "26 mai 2010"
    Range("A17").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("C2:C14").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;K:\########\CRCO.txt", _
        Destination:=Range("C2"))
        .Name = "CRCO"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Mon problème est que si je lance d'abord le .bat puis la macro cela marche, mais si je lance la macro directement, cela ne marche pas, les fichiers (ici CRCO) ne sont pas créés.

Merci d'avance.

Private Type STARTUPINFO cb As Long lpReserved As String lpDesktop As String lpTitle As String dwX As Long dwY As Long dwXSize As Long dwYSize As Long dwXCountChars As Long dwYCountChars As Long dwFillAttribute As Long dwFlags As Long wShowWindow As Integer cbReserved2 As Integer lpReserved2 As Long hStdInput As Long hStdOutput As Long hStdError As Long End Type Private Type PROCESS_INFORMATION hProcess As Long hThread As Long dwProcessID As Long dwThreadID As Long End Type Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _ hHandle As Long, ByVal dwMilliseconds As Long) As Long Private Declare Function CreateProcessA Lib "kernel32" (ByVal _ lpApplicationName As Long, ByVal lpCommandLine As String, ByVal _ lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _ ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _ ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _ lpStartupInfo As STARTUPINFO, lpProcessInformation As _ PROCESS_INFORMATION) As Long Private Declare Function CloseHandle Lib "kernel32" (ByVal _ hObject As Long) As Long Private Const NORMAL_PRIORITY_CLASS = &H20& Private Const INFINITE = -1& Private Declare Function OpenProcess Lib "kernel32" _ (ByVal dwDesiredAccess As Long, _ ByVal bInheritHandle As Long, _ ByVal dwProcessID As Long) As Long Private Declare Function GetExitCodeProcess Lib "kernel32" _ (ByVal hProcess As Long, _ lpExitCode As Long) As Long Public Sub ShellPatient(vCommand As String) Dim proc As PROCESS_INFORMATION Dim start As STARTUPINFO Dim ReturnValue As Integer ReturnValue = CreateProcessA(0&, vCommand, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc) Do ReturnValue = WaitForSingleObject(proc.hProcess, 0) DoEvents DoEvents Loop Until ReturnValue <> 258 ReturnValue = CloseHandle(proc.hProcess) End Sub