Excel macro string dependant lines range

Microsoft Windows xp professional w/serv...
January 14, 2010 at 03:11:09
Specs: Windows XP SP3, core2 duo, 2G
Hello world!

I'm a noob in VBA scripting so I decided to ask for some help on running a macro on multiple files in "current directory"

So what I'm trying to do is to run a VBS file from a batch. The vbs file should then run the code created by macro record from Excel on all the xls files in the same directory the vbs file is in. I'm sure the code has to be written somewhere but after googling for an hour I decided to ask here, maybe u can redirect me:P

Another thing that riddles me:

The code:
Sub Makro2()
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
End Sub

...arranges lines from "Range" in ascending order.
But what if I only know the first parameter from "Range" (A6), and the second should be decided based on the certain string found further down the A row, so the range length is different in every file?

Also I found this script that crashed if after dim x there was the "as" parameter...Is that syntax inappropriate in vbs scripts?

aaa, too many quiestions!:P

Thx for (if any) replies! Cheers!

See More: Excel macro string dependant lines range

Report •

January 14, 2010 at 04:41:24
re: the second should be decided based on the certain string found further down the A row

One quick answer for now:

You can determine the end of your range in various ways. Here are just 2 examples:

To find the last row in a column:

lastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A6:A" & lastRow).Select

To base the row on a specific value, e.g. MyText:

  With Columns("A")
   Set c = .Find("MyText", optional arguments)
  End With
 Range("A6:A" & c.Row).Select

(Look at Find in the VBA Help files for info on the optional arguments)

There are many other ways but we'd need more info to be more specific.


You rarely have to Select a range to perform an operation on it. It's more efficient if you just perform the operation:

Range("A6:A12").Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

Report •

January 14, 2010 at 04:59:57
Thank you for your quick answer! The MyText solution seems to be the right one for me, since I'm not looking for the last line in row A. Last line should stay where it is:)

I'll try to find the solution to run this on every file in the same directory where .vbs will be created using help, I hope I can come back here should I fail. If you have a quick answer it would help a lot. I decided to use a macro for final touches on my xls files created in batch, I'm using vb for the first time:/


Report •

January 14, 2010 at 05:55:55
To be honest, I've never written anything in vbs.

My skill set lies in writing VBA code within the Excel VBA editor. I have written a small amount of code that runs when a specific workbook is opened (using the Open event) that then accesses (copies and renames) a number of files within a directory, but again that's VBA not vbs.

Maybe someone else can step up and offer some help.

BTW...What was the issue you were having with the DIM statement?

Report •

Related Solutions

January 14, 2010 at 06:10:06
Apparently you can not initialize variables in vb scripts like DIM x as string. Right after DIM x there should be a return character or running this kind of script results in an error.

I haven't figure out the details around this behavior. I'm still struggling to figure out what is what. Isn't VBS just VBA in a script??

Cheers for all your help, I appreciate your time and effort.

Report •

January 14, 2010 at 08:25:09
As I said, I don't do VBS, but I'll take a shot here.

re: Isn't VBS just VBA in a script

In my opinion, no. VBA (Visual Basic for Applications) is a specific set of Visual Basic syntaxes specific to a given application.

The code used in Excel VBA in very different than the code used in Word VBA is very different than the code used in PowerPoint VBA, etc.

VBA is application specific, while VB and VBScript isn't.

As far as DIMing variables, a little research tells me that all variables in VBS are of the Variant type, so all you need to do is DIM them.

Once they been declared, you can set them equal to whatever you want.


<script type="text/vbscript">
Dim myVariable1, myVariable2
myVariable1 = 22
myVariable2 = "Howdy"
document.write("My number is " & myVariable1)
document.write("<br />My string is " & myVariable2)

Would display:

My number is 22 My string is Howdy

Report •

January 14, 2010 at 09:08:46
Isn't VBS just VBA in a script??
In a word? No.

VBS was (very) loosely biased off of VBA, but it has its own rules, tools, has fewer features in the language itself, designed to run in different environments, and had it had its own team. Now it's being left to languish in favor of MS' new scripting language, POWER SHELL! But that's a different rant.

To contribute, post your code, and I'll give you pointers.

Report •

January 14, 2010 at 10:01:34
One additional tip about using .Find in Excel VBA:

If you don't specify the LookAt, LookIn, etc. arguments within VBA, .Find will use whatever is currently set in the Find dialog box within Excel itself.

This also works in "reverse" in that whatever arguments you set in the VBA code will be set in the Find dialog box after you run the code.

This could impact your use of .Find in VBA and Find in Excel itself if you aren't aware of what the arguments are set for.

Report •

January 15, 2010 at 02:14:47
Thanks again for ur comments!

So I've managed to open an(one of many) xls file, run the intended macro and to get an option to save with a script:

set excel = createobject("Excel.Application") '##create excel object
excel.visible = false
excel.workbooks.open "c:\Documents and Settings\EL6360\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLS"
excel.workbooks.open "D:\##DELOVNA MAPA##\Tabele_IV\01_Tabele_IV\RP TTC.xls"

excel.Run "PERSONAL.xls!Tabele_IV"


For the script to do what I actually need I need to figure out a few more things:

1. Instead of the path D:\##DELOVNA MAPA##\Tabele_IV\01_Tabele_IV\RP TTC.xls I need to loop thru all files in the directory the script is in using something like R*.xls

#SOLVED 2.Since these are TSV files and not true excel sheets I need to save each as Excel workbooks with same filenames without excel prompting to overwrite.
excel.Application.DisplayAlerts = False
excel.ActiveWorkbook.SaveAs "C:\temp1234.xls",FileFormat=xlExcel9795
excel.Application.DisplayAlerts = True
#there is still a problem with relative path and the loop

3. If I could use the macro code in the script itself the script would work on any machine without having to record the macro everywhere I'm trying to use it. The macro is quite simple. It auto resizes all columns and makes first three lines bold.

Thanks for still putting up with me:P


Report •

January 15, 2010 at 04:43:54
What I did:

cd "01_Tabele_IV"
set _path=!CD!
set _x1=set excel = createobject("Excel.Application")
set _x2=excel.visible = false
set _x3=excel.workbooks.open "c:\Documents and Settings\EL6360\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLS"
set _x41=excel.workbooks.open "
set _x42=.xls"
set _x5=excel.Run "PERSONAL.xls^!Tabele_IV"
set _2x6=excel.Application.DisplayAlerts =
set _x71=excel.ActiveWorkbook.SaveAs "
set _x72=.xls",FileFormat=xlExcel9795
set _x8=excel.quit

for /f "tokens=1 delims=." %%A in ('dir /b R*.xls') do echo %%A>>objekti.temp

for /f "tokens=*" %%P in (objekti.temp) do (
del /Q 1.vbs

Echo !_x1!>>1.vbs
Echo !_x2!>>1.vbs
Echo !_x3!>>1.vbs
Echo !_x41!!_path!\%%P!_x42!>>1.vbs
Echo !_x5!>>1.vbs
Echo !_2x6!false>>1.vbs
Echo !_x71!!_path!\%%P!_x72!>>1.vbs
Echo !_2x6!true>>1.vbs
Echo !_x8!>>1.vbs

start /wait .\1.vbs


Haha thank you again for all the help!

Report •

Ask Question