Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

When you do a for loop in Excel VB, how do you specify that the parameter is a number in the code. More especifically, suppose I want to open 500 files, named as file1, file2, file3, ... how do you specify in the following code that "i" is a number, not a letter???

Sub CopyCells()
    For i = 1 To 500
        Workbooks.Open Filename:="filei.do"
    Next i
End Sub

I don´t know any Excel VB; I know how to program in Stata much better. My problem is that I have 500 excel files that I have to format and save before I can export to Stata without problem. To do that, I wrote a VB code. All is working well, except a for loop, because I don´t know how to say that "i" is a number, not the letter i. In Stata all you do is to write `i'.

share|improve this question

1 Answer 1

You would need to convert the variable i from a integer type to a string type first, using the CStr function should do the job:-

Sub CopyCells()
    For i = 1 To 500
        Workbooks.Open Filename:="file" + CStr(i) + ".do"
    Next i
End Sub

http://msdn.microsoft.com/en-US/library/ch47ss2a(v=vs.80).aspx

share|improve this answer
    
Thanks a lot, russ, it worked swell! –  Espinho da Flor Apr 30 '12 at 23:09
    
or use string concatenation operators with implicit conversion "file" & i & ".do" –  Aprillion May 7 '12 at 10:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.