Solved

Using a Batch File in Excel VBA

Posted on 2011-03-03
11
752 Views
Last Modified: 2012-05-11
I want to use a short batch file in an Excel macro.  I know that I must use Chr(34) to designate a double quote in the code.  What other special characters must I use the Chr() to designate the character without interferring with the VBA coding?

i have attached the batch file.  It was written by billprew to solve another issue I was having.
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26856810.html

Any help on converting this batch file to Excel VBA is appreciated.

@echo off
set BaseDir=c:\source
set DestFile=c:\dest\importfile.csv
if exist "%DestFile%" del "%DestFile%"
for %%A in ("%BaseDir%\*.csv") do (
  for /F "tokens=* usebackq" %%B in ("%%~A") do (
    echo %%B>>"%DestFile%"
  )
)

Open in new window

0
Comment
Question by:sxlowry
  • 5
  • 4
  • 2
11 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35026782
Batch files are a completely different language. Can you not simply run the batch file from VBA using Shell?
0
 

Author Comment

by:sxlowry
ID: 35028224
Yes, I can run the batch file from VBA using Shell.  

Sorry, but I was not clear on what I am trying to accomplish.  I want to do something similar to the example code below where the batch file is created in the VBA and a variable from VBA can be used.  Then Shell can be used to run the created batch file.

The snippet of code found below is from a VBA example by rondebruin.  This link will take you to the entire VBA example I am trying to modify to merge data from multiple csv files into a single csv file.
http://www.rondebruin.nl/csv.htm
The code works great, but the formatting of my csv files causes the data from the multiple single line csv files to be strung together end to end when put in the merged csv file.  The csv files are created from the measurements made by an instrument in my lab, and I have no control over the formatting from the vendor's software.

The batch file that billprew wrote will take my multiple single line csv files and put the data from each file on separate lines in the merged csv file.  I would like to incorporate the billprew code in the rondebruin code to allow me to select the folder containing the multiple csv files when the macro is run, but give me the final formatting I need.

Thanks for your help.
Steve
'Create the bat file
        Open BatFileName For Output As #1
        Print #1, "Copy " & Chr(34) & foldername & "*.csv" _
        & Chr(34) & " " & TXTFileName
        Close #1

        'Run the Bat file to collect all data from the CSV files into a TXT file
        ShellAndWait BatFileName, 0
        If Dir(TXTFileName) = "" Then
            MsgBox "There are no csv files in this folder"
            Kill BatFileName
            Exit Sub
        End If

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35028315
So what variables are you trying to incorporate when writing the code out?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:sxlowry
ID: 35028391
Foldername is the variable for the source folder containing the multiple csv files.  TXTFileName is the variable for the destination file for the merged csv file.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35028403
Yes, easiest to just Shell out to use it but....let's see if I can quickly translate (without really verifying the pieces...)

@echo off
set BaseDir=c:\source
set DestFile=c:\dest\importfile.csv
if exist "%DestFile%" del "%DestFile%"
for %%A in ("%BaseDir%\*.csv") do (
  for /F "tokens=* usebackq" %%B in ("%%~A") do (
    echo %%B>>"%DestFile%"
  )
)

--
@echo off

Irrelevant in VBA...just keeps the lines from showing and VB won't show lines

--
set BaseDir=c:\source
set DestFile=c:\dest\importfile.csv

Setting variables in VB is pretty straight-forward (although you should also define them):

Dim BaseDir as string
Dim DestFile as string
BaseDir="c:\source"
DestFile="c:\dest\importfile.csv"

--
if exist "%DestFile%" del "%DestFile%"

A bit trickier in VB; one way is this:
if Dir(DestFile) <> "" Then
  Kill DestFile
endif

--
for %%A in ("%BaseDir%\*.csv") do (
  for /F "tokens=* usebackq" %%B in ("%%~A") do (
    echo %%B>>"%DestFile%"
  )
)

Again, a bit tricker in VB.  Let me tackle in pieces

--
for %%A in ("%BaseDir%\*.csv") do (

%%A is just a variable, the "for" is a loop, the "in" applies to a set of files that we'll have to extract

Dim A as string
A=Dir(BaseDir & "\*.csv")
Do Until A=""
   '...more to come...
   A=Dir
Loop

--
With that out of the way, let's tackle the inner loop

  for /F "tokens=* usebackq" %%B in ("%%~A") do (
    echo %%B>>"%DestFile%"
  )

I've never used the /F option but it appears to handle file parsing of each file.  That might need more work to piece out but see if something like this works:

Dim B as string
Open A for binary as #1
B=input(lof(1),1)
Close #1
Open DestFile for append as #1
Print#1, B
Close #1
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 35028435
Piecing it all together you get something like this (plus a minor correction on the first "Open"):

Dim BaseDir as string
Dim DestFile as string
Dim A as string
Dim B as string

BaseDir="c:\source"
DestFile="c:\dest\importfile.csv"

if Dir(DestFile) <> "" Then
  Kill DestFile
endif

A=Dir(BaseDir & "\*.csv")
Do Until A=""
   '...more to come...
Open BaseDir & "\"& A for binary as #1
B=input(lof(1),1)
Close #1
Open DestFile for append as #1
Print#1, B
Close #1
   A=Dir
Loop
0
 

Author Comment

by:sxlowry
ID: 35031673
rspahitz,
What does the comment statement '...more to come... mean?   Is there more code that needs to be added?

Thanks.
Steve
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35031725
oh, that should have come out...that was copied from previous code above.

I will be out until Monday.  If you need help before then, hopefully one of the other experts can assist until then.
0
 

Author Comment

by:sxlowry
ID: 35031735
Thanks for your help.  Have a great weekend.
0
 

Author Closing Comment

by:sxlowry
ID: 35031956
The code works great.  Thanks for your help.

Steve
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35048096
Back from my trip.
Glad to hear that it worked as needed.  I expected that you would need a few minor mods but you were probably able to work through them.
Thanks for the A grade.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question