converting many steps into a VBA

[fruehling]

Hello,

Excel 2003
As the first part of a VBA I am trying to write (my first), I need to combine all the following steps that I currently have in separate cells in the spreadsheet. Please bear with me as I am stumbling through all this:


1. open file "L:\PCS-Projects\Commissions-BusOps\Dealer Contact List\Dealer Contact List.xls
(this file contains the number needed for the next step)

2. =INDEX('L:\PCS-Projects\Commissions-BusOps\Dealer Contact List\[Dealer Contact List.xls]Complete List'!$C:$C,MATCH($C$3,'L:\PCS-Projects\Commissions-BusOps\Dealer Contact List\[Dealer Contact List.xls]Complete List'!$D:$D,0))
(result is the dealer code, in this example it is 2340)

3. =TEXT(MONTH(P3),"00")&RIGHT(TEXT(YEAR(P3),"00"),2)
(result is mmyy of the month being worked on 1108)

4. =CONCATENATE(BF6,BF5,"ALL_",A3)
(this puts together the above two results, followed by ALL_ and the dealer name in question 11082340ALL_Jakes wireless of DC, LLC)

5. =SUBSTITUTE(BF14," ","")
(this removes the spaces 11082340ALL_JakeswirelessofDC,LLC)

6. =SUBSTITUTE(BF14,",","")
(this removes the commas 11082340ALL_JakeswirelessofDCLLC)

7. =BF15 & ".xls"
(this puts .xls at the end to give me the final file name 11082340ALL_JakeswirelessofDCLLC.xls)

8. Then I need to open that file to do some more things.


Any help is greatly appreciated!

 

[lenze]

I suggest you try the macro recorder while doing these steps. It won't give you everything, but it should get you started enough to be able to ask specific questions
lenze

 

[fruehling]

Thank you for the reply. I did as you suggested, but all it did was enter these formulas into the cells with VBA. I was to avoid using any cells for this and have it do all this just in VBA. The result will be a file name that I will open with VBA then pull information from to input into cells. Is the only way to do this by having each step go into a cell?