Re: Excel VBA Help by Bob
Bob
Fri Sep 08 14:13:49 CDT 2006
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.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"news.microsoft.com" <rmsuffyNO@SPAMhotmail.com> wrote in message
news:u2lPHY30GHA.2636@TK2MSFTNGP06.phx.gbl...
> 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
>
>
>
>
> "Bob Phillips" <bob.NGs@somewhere.com> wrote in message
> news:OseYxl20GHA.1300@TK2MSFTNGP05.phx.gbl...
> > 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
> >
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "news.microsoft.com" <rmsuffyNO@SPAMhotmail.com> wrote in message
> > news:uolAbb20GHA.1268@TK2MSFTNGP02.phx.gbl...
> >> 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
> >>
> >>
> >
> >
>
>