.bat file wont save a .log file when triggered in VBA

Question

Ok, so this is a somewhat complicated question, but hopefully somebody can help me out. I created a .bat file to help with batch process data uploads to SalesForce servers. I then tied excel macros to the .bat files by having the .bat files called in Excel VBA.

Here's my problem: Whenever the .bat files are called in VBA, the .log file isn't saved - it's not even executed (almost like the line of code didn't exist in the .bat file).

Now here's the weird part: if I just click on the .bat file to begin the upload process -- as opposed to calling the .bat from within VBA -- the .log file is overwritten and a new one is created.

How can I get the .bat file to save a .log regardless of whether its fired simply by clicking on the .bat file, or from within VBA? This is very frustrating - any and all help is greatly appreciated. Thank you.

Here's my MS-DOS (batch) code:

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org /dtd/spring-beans.dtd">
<beans>
    <bean id="Update_Listing_Val_FNMA" class="com.salesforce.dataloader.process.ProcessRunner" singleton="false">
        <description>Created by Dataloader Cliq.</description>
        <property name="name" value="Update_Listing_Val_FNMA"/>
        <property name="configOverrideMap">
            <map>
                <entry key="dataAccess.name" value="M:\My Folder\CSV Files\salesforce.com\Apex Data Loader 22.0\cliq_process\Update_Listing_Val_FNMA\read\Update_Listing_Val_FNMA.csv"/>
                <entry key="dataAccess.readUTF8" value="true"/>
                <entry key="dataAccess.type" value="csvRead"/>
                <entry key="dataAccess.writeUTF8" value="true"/>
                <entry key="process.enableExtractStatusOutput" value="true"/>
                <entry key="process.enableLastRunOutput" value="true"/>
                <entry key="process.lastRunOutputDirectory" value="M:\My Folder\CSV Files\salesforce.com\Apex Data Loader 22.0\cliq_process\Update_Listing_Val_FNMA\log"/>
                <entry key="process.mappingFile" value="M:\My Folder\CSV Files\salesforce.com\Apex Data Loader 22.0\cliq_process\Update_Listing_Val_FNMA\config\Update_Listing_Val_FNMA.sdl"/>
                <entry key="process.operation" value="update"/>
                <entry key="process.statusOutputDirectory" value="M:\My Folder\CSV Files\salesforce.com\Apex Data Loader 22.0\cliq_process\Update_Listing_Val_FNMA\log"/>
                <entry key="sfdc.bulkApiCheckStatusInterval" value="5000"/>
                <entry key="sfdc.bulkApiSerialMode" value="5000"/>
                <entry key="sfdc.debugMessages" value="false"/>
                <entry key="sfdc.enableRetries" value="true"/>
                <entry key="sfdc.endpoint" value="https://www.salesforce.com/services/Soap/u/22.0"/>
                <entry key="sfdc.entity" value="QA_FNMA__c"/>
                <entry key="sfdc.extractionRequestSize" value="500"/>
                <entry key="sfdc.insertNulls" value="false"/>
                <entry key="sfdc.loadBatchSize" value="100"/>
                <entry key="sfdc.maxRetries" value="3"/>
                <entry key="sfdc.minRetrySleepSecs" value="2"/>
                <entry key="sfdc.noCompression" value="false"/>
                <entry key="sfdc.password" value="XXXXXXXXXXXXXXXXXX"/>
                <entry key="sfdc.proxyHost" value=""/>
                <entry key="sfdc.proxyNtlmDomain" value=""/>
                <entry key="sfdc.proxyPassword" value=""/>
                <entry key="sfdc.proxyPort" value=""/>
                <entry key="sfdc.proxyUsername" value=""/>
                <entry key="sfdc.timeoutSecs" value="60"/>
                <entry key="sfdc.useBulkApi" value="false"/>
                <entry key="sfdc.username" value="XXXXXXXXXXXXXXXXXX"/>
            </map>
        </property>
    </bean>
</beans>

** EDIT - 2/13/14 1649 **

Here's the call from VBA part:

Shell "M:\My Folder\XXXXXXXXXXXX\CSV Files\salesforce.com\Apex Data Loader 22.0\cliq_process\O PKG Status\Update_O_PKG_Status_REAC\Update_O_PKG_Status_REAC.bat"

Application.Wait (Now + TimeValue("0:00:12"))

    Sheets("sheet5").Select

    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs Filename:="M:\My Folder\XXXXXXXXXXXXXXX\CSV Files\salesforce.com\Apex Data Loader 22.0\cliq_process\O PKG Status\Update_O_PKG_Status_FNMA_Reactive\read\Update_O_PKG_Status_FNMA_Reactive", FileFormat:=xlCSV

       newFileName = "FREAC O Pkg" & Format(Now, " MM-DD HHMM") & ".csv"

** EDIT - 2/18/14 1221 **

Here's the log-conf file; it's an XML doc.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration>
    <appender name="fileAppender" class="org.apache.log4j.RollingFileAppender">
        <param name="File"   value="log/sdl.log" />
        <param name="Append" value="true" />
        <param name="MaxFileSize" value="100KB" />
        <param name="MaxBackupIndex" value="1" />
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%d %-5p [%t] %C{2} %M (%F:%L) - %m%n"/>
        </layout>
    </appender>
    <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%d %-5p [%t] %C{2} %M (%F:%L) - %m%n"/>
        </layout>
    </appender>
    <category name="org.apache.log4j.xml">
        <priority value="warn" />
        <appender-ref ref="fileAppender" />
        <appender-ref ref="STDOUT" />
    </category>
    <logger name="org.apache" >
        <level value ="warn" />
    </logger>
    <root>
        <priority value ="info" />
        <appender-ref ref="fileAppender" />
        <appender-ref ref="STDOUT" />
    </root>
</log4j:configuration>

** EDIT - 2/19/14 1226 **

Here's the actual .batch file that is calling everything;

SET DLPATH="M:\My Folder\XXXXXXXXXXXXXXXXX\CSV Files\salesforce.com\Apex Data Loader 22.0"
SET DLCONF="M:\My Folder\XXXXXXXXXXXXXXXXX\CSV Files\salesforce.com\Apex Data Loader 22.0\cliq_process\Update_Listing_Val_FNMA\config"
SET DLDATA="M:\My Folder\XXXXXXXXXXXXXXXXX\CSV Files\salesforce.com\Apex Data Loader 22.0\cliq_process\Update_Listing_Val_FNMA\write"
call %DLPATH%\_jvm\bin\java.exe -cp %DLPATH%\DataLoader.jar -Dsalesforce.config.dir=%DLCONF% com.salesforce.dataloader.process.ProcessRunner process.name=Update_Listing_Val_FNMA
REM To rotate your export files, uncomment the line below
REM copy %DLDATA%\Update_Listing_Val_FNMA.csv %DLDATA%\%date:~10,4%%date:~7,2%%date:~4,2%-%time:~0,2%-Update_Listing_Val_FNMA.csv

Since your .bat file is working when launched alone, it should be the call from VBA. How did you call it? Can you show us the code from the VBA part? — simpLE MAn, Feb 13 '14 at 02:57
Here's the code from the VBA Part. Any ideas as to why it's erroring out? It's really frustrating that the .bat executes just fine when launched alone, but doesn't some to function properly when called from VBA. It's very peculiar. — Jay M., Feb 14 '14 at 00:48
Suggest you post your batch code. Might as well leave the XML there though. — Magoo, Feb 14 '14 at 02:24
I did a simple test creating a batch file that just echo'd out some text to a file and called it from Excel VBA using Shell. It created the output file just fine in the directory where the script was. Post your batch file because something in it isn't right. I'm guessing it's outputting a file _Somewhere_ just not where you expect it to. — Matt Williamson, Feb 14 '14 at 14:07
Hey all, the part where I tell the batch file to save the log is in the code I already posted. It's the node that tells the uploader to save a log file; the node is called "process.statusOutputDirectory." I also posted the log-conf.xml file - hopefully this is what you guys are looking for? — Jay M., Feb 18 '14 at 20:17

.bat file wont save a .log file when triggered in VBA

0 Answers0