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
Solved

Using a Batch File in Excel VBA

Posted on 2011-03-03
11
760 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
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!
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

791 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