Solved

Using a Batch File in Excel VBA

Posted on 2011-03-03
11
783 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:Steve Lowry
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Steve Lowry
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

Author Comment

by:Steve Lowry
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:Steve Lowry
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:Steve Lowry
ID: 35031735
Thanks for your help.  Have a great weekend.
0
 

Author Closing Comment

by:Steve Lowry
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

617 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