Excel VBA Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

news.microsoft.com - 08 Sep 2006 17:50 GMT

I'm really confused. I thought the following code would work but it isn't
doing what I expected. I have several data files that are exactly the same
and I need to reformat all of them... Instead of opening each one and doing
the formatting needed, I thought it would be nice to have a macro open the
all the xls files in a given directory and format them, and then save &
close them. Does anyone know what is wrong with my code below?

Public Sub ImportData()

'Define variables
Dim sPath As String
Dim sFile As String
Dim oExcel As New Excel.Application
Dim oWB As New Workbook

'Loop through the xls files in the directory...
sPath = ActiveWorkbook.Path
sFile = Dir(sPath & "\*.xls")

Do While sFile <> ""

'Open xls data file that needs to be formatted...
If sFile <> "Master Import File.xls" Then
Debug.Print sFile
Set oWB = oExcel.Workbooks.Open(sPath & "\" & sFile)
With oWB
.Activate
Call Formatting
.Close
End With
Set oWB = Nothing
End If
sFile = Dir
Loop
Set oExcel = Nothing

End Sub

Sub Formatting()
ActiveSheet.Rows("1:7").Select
Selection.Delete Shift:=xlUp
ActiveSheet.Columns("A:A").ColumnWidth = 83.14
ActiveSheet.Cells.Select
With Selection
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveSheet.Columns("B:D").Select
Selection.Delete Shift:=xlToLeft
ActiveSheet.Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
ActiveSheet.Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
ActiveSheet.Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
ActiveSheet.Columns("H:I").Select
ActiveSheet.Range("I1").Activate
Selection.Delete Shift:=xlToLeft
ActiveSheet.Columns("B:H").Select
Selection.ColumnWidth = 15
ActiveSheet.Range("A1").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
Selection.Interior.ColorIndex = 6
ActiveWorkbook.Save
End Sub

Thanks!

Rob

Reply to this Message

Bob Phillips - 08 Sep 2006 18:08 GMT

Give us a clue as to where the problem is. What does it do/not do?

BTW, the code can be tidied a bit, and I presume you 3wan t to delet the
columns right to left, not left to right.

Option Explicit

Public Sub ImportData()
'Define variables
Dim sPath As String
Dim sFile As String
Dim oExcel As New Excel.Application
Dim oWB As New Workbook

'Loop through the xls files in the directory...
sPath = ActiveWorkbook.Path
sFile = Dir(sPath & "\*.xls")

Do While sFile <> ""

'Open xls data file that needs to be formatted...
If sFile <> "Master Import File.xls" Then
Debug.Print sFile
Set oWB = oExcel.Workbooks.Open(sPath & "\" & sFile)
With oWB
Call Formatting
.Save
.Close
End With
Set oWB = Nothing
End If
sFile = Dir
Loop
Set oExcel = Nothing

End Sub

Sub Formatting()
With ActiveSheet
.Rows("1:7").Delete Shift:=xlUp
.Columns("A:A").ColumnWidth = 83.14
With .Cells
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
.Columns("H:I").Delete Shift:=xlToLeft
.Columns("G:G").Delete Shift:=xlToLeft
.Columns("E:E").Delete Shift:=xlToLeft
.Columns("B:D").Delete Shift:=xlToLeft
.Columns("B:H").ColumnWidth = 15
.Range(.Range("A1"),
.Range("A1").End(xlToRight)).Interior.ColorIndex = 6
End With
End Sub

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I'm really confused. I thought the following code would work but it isn't
> doing what I expected. I have several data files that are exactly the same
[quoted text clipped - 69 lines]
>
> Rob

Reply to this Message

news.microsoft.com - 08 Sep 2006 19:38 GMT

Hi everyone,

I believe the problem is when the file that needs to be formatted is opened
at this part of the code (in the ImportData sub):

Set oWB = oExcel.Workbooks.Open(sPath & "\" & sFile)
With oWB
Call Formatting
.Save
.Close
End With

When the "Call Formatting" line runs, the formatting sub formats the "Master
Import File" (where the vba code is) and not the file that was opened above.

Bob, thanks for your suggestion on cleaning up the code -- I am assuming
you're referring to the Formatting sub... I created that one on the fly by
recording a macro... so I really do want to delete left to ride and not the
other way around. :-)

Thanks,

Rob

> Give us a clue as to where the problem is. What does it do/not do?
>
[quoted text clipped - 132 lines]
>>
>> Rob

Reply to this Message

Bob Phillips - 08 Sep 2006 20:13 GMT

If that is the problem Rob, you could always activate the other sheet

Set oWB = oExcel.Workbooks.Open(sPath & "\" & sFile)
With oWB
.Worksheets(1).Activate
Call Formatting
.Save
.Close
End With

or better still, pass the appropriate worksheet to the formatting routine
and use that in the routine.

Deleteing left to right is not recommended as it is so easy to lose track of
where you are (column H suddenly becomes column G when you delete column C).
I know you recorded it that way, but I would work it the other way even when
recording.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hi everyone,
>
[quoted text clipped - 156 lines]
> >>
> >> Rob

Reply to this Message

Rate this thread: