Solved

Using a Batch File in Excel VBA

Posted on 2011-03-03
11
733 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now