CODEWITHSUNDEEP
vbaexcel
Evan
Evan

Reputation: 618

Formating Excel Worksheet from a different workbook's VBA

I am working with Multiple Excel files (Copying information from a master file into smaller more specific files for reporting) and am having trouble getting my master file to format the area files while it is putting information in them. Here is the relevant code:

Dim xl As Excel.Application
Dim I_workbook As Excel.Workbook
Dim ws As Excel.Worksheet
Set xl = CreateObject("Excel.Application")
Set I_workbook = xl.Workbooks.Open("H:\....")
Set ws = I_workbook.Sheets("Sheet1")

'... Enter information into the ws. 
      'This part works so I know I have proper objects and such



Dim rng As Range
For Each rng In ws.Range("B2", "B" & (BotRow - TopRow + 2))
        rng.Select
        Selection.NumberFormat = "General"
        'rng.NumberFormat = "General"
Next rng

I_workbook.Save
xl.Quit

I am trying to take all the cells that I have put information into and format them as a number. Somtimes they are numbers and sonetimes they are numbers followed by a letter. Their source is a text field in a database so excel reads them as text and then puts a nice annoying green triangle in the corner that annoys someone high up so here I sit...

The commented line rng.NumberFormat = "General" is another attempt of mine that did not work.

Thanks

Upvotes: 1

Views: 63

Answers (1)

Rory
Rory

Reputation: 34075

rng.Numberformat should work unless there's more to the story, but you don't actually need to loop to do that:

ws.Range("B2", "B" & (BotRow - TopRow + 2)).Numberformat = "General"

will suffice. If the data in the cells is numbers stored as text, you can use something like this:

With ws.Range("B2", "B" & (BotRow - TopRow + 2))
   .Numberformat = "General"
   .Value2 = .Value2
End With

Upvotes: 2

Related Questions