Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using a Batch File in Excel VBA

Posted on 2011-03-03
11
Medium Priority
?
796 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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;…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
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…

721 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