Looping with variables 2

[AnotherAlan]

Hi All,

Is it possible to use a variable within an Open statement to enable looping?

Basically, I have a number of log files from Backup Exec that I pull into Excel to retrieve job data e.t.c

Open "X:\WINTELbackups\Backup Logs\UKLDNSERV1\BEX60.txt" For Input As #1

What I would like to be able to do is create a loop around the UKLDNSERV* and/or the BEX* portion to avoid the currently manual intervention to change the log file name each time.

i.e for server in UKLDNSERV1 UKLDNSERV2 UKLDNSERV3;do
OPEN "The path to the log files\BEX*.txt"
<Process the info>
done

If this cannot be done at the server level, can it be done at the BEX file level?

Sorry if this is confusing. I worte the code to this a couple of years ago but moved into the unix world and away from VBA. I do remember having trouble getting it to work back then though. BUT, back then I didn't know about tek-tips!!!

All help appreciated.
Alan

 

[HarleyQuinn]

Something like:

Dim i as Integer
Dim j as Integer

For i = 1 to <insert your number of servers>
For j = 1 to <insert your number of files>

Open "X:\WINTELbackups\Backup Logs\UKLDNSERV" & i & "\BEX" & j & ".txt"

Next j
Next i

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[AnotherAlan]

Marvellous, thank you very much, works like a charm.
Alan

 

[HarleyQuinn]

Glad I could help, thanks for the star

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[AnotherAlan]

Actually, one more question if I may be so bold.
With the BEX files they are not always in sequential order, which is a bit of a pain.
They could be called anything from BEX01 to BEX99.

Would there be a way of using a wildcard for this part?
Or even getting the code to process every file it finds in that path?
I appreciate that this is a little more complex now than just a straightforward loop.

Thanks again.
Alan

 

[PHV]

Have a look at the Dir function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AnotherAlan]

Ah, thanks PHV. So;

To iterate over all files in a folder, specify an empty string:

Dir("")

I'll give it a go.

Much obliged.

 

[PHV]

For i = 1 to <insert your number of servers>
strPath = "X:\WINTELbackups\Backup Logs\UKLDNSERV" & i & "\"
strFile = Dir(strPath & "BEX*.txt")
While strFile <> ""
Open strPath & strFile For Input As #1
...
Close #1
strFile = Dir
WEnd
Next i

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

No code tags? How long have you been a member here PHV?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[AnotherAlan]

Guys, you work fast, I've just been trying it for myself only to find that you're way ahead of me;

I came up with this solution that will list the files in a separate sheet and I was going to pick them up from there, but your solution is much more elegant.

Public Sub listfiles()
Dim lofile As Variant
Dim i As Integer
i = 0
logfile = Dir("X:\WINTELbackups\Backup Logs\UKLDNBACK3\*.txt")
While fname <> ""
i = i + 1
Me.Cells(i, 1).Value = logfile
logfile = Dir()
Wend
End Sub

Thanks for your help and pointers.
It's been fun playing with VBA again after all these years...still prefer unix scripting though ;-)

Much obliged
Alan

 

[HarleyQuinn]

Just a small point, I think your While loop should read:

While logfile <> ""

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Ignore my last post, hadn't realised that wasn't the code you were going to be using

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[AnotherAlan]

Oops, quite right, havn't tested it yet luckily.
I should also point out that when I wrote "came up with this solution" I meant "found this solution", so thanks and credit should go to macropod for an earlier post.

Cheers all.