Automating Excel from Access

From Access I'm trying to set the vertical alignment for all cells of an 
Excelfile to 'Top':

Dim XLApp As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet

Set XLApp = New Excel.Application
Set XLBook = XLApp.Workbooks.Open("c:\Test.xls")
Set XLSheet = XLBook.Worksheets(1)
XLSheet.Activate
XLSheet.Cells.Select

With Selection
    .VerticalAlignment = xlTop
End With

XLBook.SaveAs ("c:\Test2.xls")
Set XLSheet = Nothing
XLBook.Close
Set XLBook = Nothing
XLApp.Quit
Set XLApp = Nothing

but I get the message objetvariable or blockvariable With is not set.
..VerticalAlignment = xlTop
is marked yellow.

Can someone see from the code above what I'm doing wrong?

Thanks,

Lars 


0
Lars5404 (8)
4/12/2009 4:11:48 PM
excel 39879 articles. 2 followers. Follow

8 Replies
554 Views

Similar Articles

[PageSpeed] 0

You haven't defined the variable `Selection'. Excel does this automatically 
in an Excel macro so you probably forgot. Anyway a better option is to do:

with XLSheet.Cells
  ...
end with

"Lars Brownies" <Lars@Brownies.com> wrote in message 
news:grt3s3$tb4$1@textnews.wanadoo.nl...
> From Access I'm trying to set the vertical alignment for all cells of an 
> Excelfile to 'Top':
>
> Dim XLApp As Excel.Application
> Dim XLBook As Excel.Workbook
> Dim XLSheet As Excel.Worksheet
>
> Set XLApp = New Excel.Application
> Set XLBook = XLApp.Workbooks.Open("c:\Test.xls")
> Set XLSheet = XLBook.Worksheets(1)
> XLSheet.Activate
> XLSheet.Cells.Select
>
> With Selection
>    .VerticalAlignment = xlTop
> End With
>
> XLBook.SaveAs ("c:\Test2.xls")
> Set XLSheet = Nothing
> XLBook.Close
> Set XLBook = Nothing
> XLApp.Quit
> Set XLApp = Nothing
>
> but I get the message objetvariable or blockvariable With is not set.
> .VerticalAlignment = xlTop
> is marked yellow.
>
> Can someone see from the code above what I'm doing wrong?
>
> Thanks,
>
> Lars
> 

0
4/12/2009 5:51:02 PM
I agree with Yawar's response, but you don't need to declare Selection.

But would have to qualify it.

With XLApp.Selection

But it's better to drop the .select and just use:
with XLSheet.Cells



Lars Brownies wrote:
> 
> From Access I'm trying to set the vertical alignment for all cells of an
> Excelfile to 'Top':
> 
> Dim XLApp As Excel.Application
> Dim XLBook As Excel.Workbook
> Dim XLSheet As Excel.Worksheet
> 
> Set XLApp = New Excel.Application
> Set XLBook = XLApp.Workbooks.Open("c:\Test.xls")
> Set XLSheet = XLBook.Worksheets(1)
> XLSheet.Activate
> XLSheet.Cells.Select
> 
> With Selection
>     .VerticalAlignment = xlTop
> End With
> 
> XLBook.SaveAs ("c:\Test2.xls")
> Set XLSheet = Nothing
> XLBook.Close
> Set XLBook = Nothing
> XLApp.Quit
> Set XLApp = Nothing
> 
> but I get the message objetvariable or blockvariable With is not set.
> .VerticalAlignment = xlTop
> is marked yellow.
> 
> Can someone see from the code above what I'm doing wrong?
> 
> Thanks,
> 
> Lars

-- 

Dave Peterson
0
petersod (12005)
4/12/2009 6:22:31 PM
Thanks Yawar, Dave,
Works like a charm! See the (final) code below. Still, I have the feeling 
this code can be written more efficiently, especially the with statement.

If you have any additional hints/tips, I'd love to hear them.

Lars

Private Sub btnEditExcel_Click()
Dim XLApp As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet

On Error GoTo btnEditExcel_Click_Error

Set XLApp = New Excel.Application
Set XLBook = XLApp.Workbooks.Open("c:\Map16.xls")
Set XLSheet = XLBook.Worksheets(1)

With XLSheet
    .Activate
    .Cells.VerticalAlignment = xlTop
    .Range("A2").Select
    XLApp.ActiveWindow.FreezePanes = True
    .Rows("1:1").Select
    XLApp.Selection.Font.Bold = True
    XLApp.Selection.AutoFilter
    .Cells(1, 1).Select
    XLBook.Save
End With

Exit_this_sub:
    Set XLSheet = Nothing
    XLBook.Close
    Set XLBook = Nothing
    XLApp.Quit
    Set XLApp = Nothing
    Exit Sub
btnEditExcel_Click_Error:
   MsgBox "Error " & Err.Number & " :" & Err.Description
   Resume Exit_this_sub
End Sub


"Dave Peterson" <petersod@verizonXSPAM.net> schreef in bericht 
news:49E23167.663BF388@verizonXSPAM.net...
>I agree with Yawar's response, but you don't need to declare Selection.
>
> But would have to qualify it.
>
> With XLApp.Selection
>
> But it's better to drop the .select and just use:
> with XLSheet.Cells
>
>
>
> Lars Brownies wrote:
>>
>> From Access I'm trying to set the vertical alignment for all cells of an
>> Excelfile to 'Top':
>>
>> Dim XLApp As Excel.Application
>> Dim XLBook As Excel.Workbook
>> Dim XLSheet As Excel.Worksheet
>>
>> Set XLApp = New Excel.Application
>> Set XLBook = XLApp.Workbooks.Open("c:\Test.xls")
>> Set XLSheet = XLBook.Worksheets(1)
>> XLSheet.Activate
>> XLSheet.Cells.Select
>>
>> With Selection
>>     .VerticalAlignment = xlTop
>> End With
>>
>> XLBook.SaveAs ("c:\Test2.xls")
>> Set XLSheet = Nothing
>> XLBook.Close
>> Set XLBook = Nothing
>> XLApp.Quit
>> Set XLApp = Nothing
>>
>> but I get the message objetvariable or blockvariable With is not set.
>> .VerticalAlignment = xlTop
>> is marked yellow.
>>
>> Can someone see from the code above what I'm doing wrong?
>>
>> Thanks,
>>
>> Lars
>
> -- 
>
> Dave Peterson 


0
Lars5404 (8)
4/12/2009 9:17:40 PM
You need to activate the sheet to use the .freezepanes, but I'd use something
like:

With XLSheet
    .Activate
    .Cells.VerticalAlignment = xlTop
    .Range("a1").select  'make sure A1 is visible
    .Range("A2").Select
    XLApp.ActiveWindow.FreezePanes = True
    .Rows(1).Font.Bold = True
    .rows(1).autofilter
    .Cells(1, 1).Select
End With
XLBook.Save

Lars Brownies wrote:
> 
> Thanks Yawar, Dave,
> Works like a charm! See the (final) code below. Still, I have the feeling
> this code can be written more efficiently, especially the with statement.
> 
> If you have any additional hints/tips, I'd love to hear them.
> 
> Lars
> 
> Private Sub btnEditExcel_Click()
> Dim XLApp As Excel.Application
> Dim XLBook As Excel.Workbook
> Dim XLSheet As Excel.Worksheet
> 
> On Error GoTo btnEditExcel_Click_Error
> 
> Set XLApp = New Excel.Application
> Set XLBook = XLApp.Workbooks.Open("c:\Map16.xls")
> Set XLSheet = XLBook.Worksheets(1)
> 
> With XLSheet
>     .Activate
>     .Cells.VerticalAlignment = xlTop
>     .Range("A2").Select
>     XLApp.ActiveWindow.FreezePanes = True
>     .Rows("1:1").Select
>     XLApp.Selection.Font.Bold = True
>     XLApp.Selection.AutoFilter
>     .Cells(1, 1).Select
>     XLBook.Save
> End With
> 
> Exit_this_sub:
>     Set XLSheet = Nothing
>     XLBook.Close
>     Set XLBook = Nothing
>     XLApp.Quit
>     Set XLApp = Nothing
>     Exit Sub
> btnEditExcel_Click_Error:
>    MsgBox "Error " & Err.Number & " :" & Err.Description
>    Resume Exit_this_sub
> End Sub
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> schreef in bericht
> news:49E23167.663BF388@verizonXSPAM.net...
> >I agree with Yawar's response, but you don't need to declare Selection.
> >
> > But would have to qualify it.
> >
> > With XLApp.Selection
> >
> > But it's better to drop the .select and just use:
> > with XLSheet.Cells
> >
> >
> >
> > Lars Brownies wrote:
> >>
> >> From Access I'm trying to set the vertical alignment for all cells of an
> >> Excelfile to 'Top':
> >>
> >> Dim XLApp As Excel.Application
> >> Dim XLBook As Excel.Workbook
> >> Dim XLSheet As Excel.Worksheet
> >>
> >> Set XLApp = New Excel.Application
> >> Set XLBook = XLApp.Workbooks.Open("c:\Test.xls")
> >> Set XLSheet = XLBook.Worksheets(1)
> >> XLSheet.Activate
> >> XLSheet.Cells.Select
> >>
> >> With Selection
> >>     .VerticalAlignment = xlTop
> >> End With
> >>
> >> XLBook.SaveAs ("c:\Test2.xls")
> >> Set XLSheet = Nothing
> >> XLBook.Close
> >> Set XLBook = Nothing
> >> XLApp.Quit
> >> Set XLApp = Nothing
> >>
> >> but I get the message objetvariable or blockvariable With is not set.
> >> .VerticalAlignment = xlTop
> >> is marked yellow.
> >>
> >> Can someone see from the code above what I'm doing wrong?
> >>
> >> Thanks,
> >>
> >> Lars
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
4/12/2009 10:37:57 PM
>> You need to activate the sheet to use the .freezepanes

What exactly do you mean by this?

Lars

"Dave Peterson" <petersod@verizonXSPAM.net> schreef in bericht 
news:49E26D45.940A62ED@verizonXSPAM.net...
> You need to activate the sheet to use the .freezepanes, but I'd use 
> something
> like:
>
> With XLSheet
>    .Activate
>    .Cells.VerticalAlignment = xlTop
>    .Range("a1").select  'make sure A1 is visible
>    .Range("A2").Select
>    XLApp.ActiveWindow.FreezePanes = True
>    .Rows(1).Font.Bold = True
>    .rows(1).autofilter
>    .Cells(1, 1).Select
> End With
> XLBook.Save
>
> Lars Brownies wrote:
>>
>> Thanks Yawar, Dave,
>> Works like a charm! See the (final) code below. Still, I have the feeling
>> this code can be written more efficiently, especially the with statement.
>>
>> If you have any additional hints/tips, I'd love to hear them.
>>
>> Lars
>>
>> Private Sub btnEditExcel_Click()
>> Dim XLApp As Excel.Application
>> Dim XLBook As Excel.Workbook
>> Dim XLSheet As Excel.Worksheet
>>
>> On Error GoTo btnEditExcel_Click_Error
>>
>> Set XLApp = New Excel.Application
>> Set XLBook = XLApp.Workbooks.Open("c:\Map16.xls")
>> Set XLSheet = XLBook.Worksheets(1)
>>
>> With XLSheet
>>     .Activate
>>     .Cells.VerticalAlignment = xlTop
>>     .Range("A2").Select
>>     XLApp.ActiveWindow.FreezePanes = True
>>     .Rows("1:1").Select
>>     XLApp.Selection.Font.Bold = True
>>     XLApp.Selection.AutoFilter
>>     .Cells(1, 1).Select
>>     XLBook.Save
>> End With
>>
>> Exit_this_sub:
>>     Set XLSheet = Nothing
>>     XLBook.Close
>>     Set XLBook = Nothing
>>     XLApp.Quit
>>     Set XLApp = Nothing
>>     Exit Sub
>> btnEditExcel_Click_Error:
>>    MsgBox "Error " & Err.Number & " :" & Err.Description
>>    Resume Exit_this_sub
>> End Sub
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> schreef in bericht
>> news:49E23167.663BF388@verizonXSPAM.net...
>> >I agree with Yawar's response, but you don't need to declare Selection.
>> >
>> > But would have to qualify it.
>> >
>> > With XLApp.Selection
>> >
>> > But it's better to drop the .select and just use:
>> > with XLSheet.Cells
>> >
>> >
>> >
>> > Lars Brownies wrote:
>> >>
>> >> From Access I'm trying to set the vertical alignment for all cells of 
>> >> an
>> >> Excelfile to 'Top':
>> >>
>> >> Dim XLApp As Excel.Application
>> >> Dim XLBook As Excel.Workbook
>> >> Dim XLSheet As Excel.Worksheet
>> >>
>> >> Set XLApp = New Excel.Application
>> >> Set XLBook = XLApp.Workbooks.Open("c:\Test.xls")
>> >> Set XLSheet = XLBook.Worksheets(1)
>> >> XLSheet.Activate
>> >> XLSheet.Cells.Select
>> >>
>> >> With Selection
>> >>     .VerticalAlignment = xlTop
>> >> End With
>> >>
>> >> XLBook.SaveAs ("c:\Test2.xls")
>> >> Set XLSheet = Nothing
>> >> XLBook.Close
>> >> Set XLBook = Nothing
>> >> XLApp.Quit
>> >> Set XLApp = Nothing
>> >>
>> >> but I get the message objetvariable or blockvariable With is not set.
>> >> .VerticalAlignment = xlTop
>> >> is marked yellow.
>> >>
>> >> Can someone see from the code above what I'm doing wrong?
>> >>
>> >> Thanks,
>> >>
>> >> Lars
>> >
>> > --
>> >
>> > Dave Peterson
>
> -- 
>
> Dave Peterson 


0
Lars5404 (8)
4/13/2009 4:34:33 PM
Try freezing the panes on a sheet/window that isn't active.

Lars Brownies wrote:
> 
> >> You need to activate the sheet to use the .freezepanes
> 
> What exactly do you mean by this?
> 
> Lars
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> schreef in bericht
> news:49E26D45.940A62ED@verizonXSPAM.net...
> > You need to activate the sheet to use the .freezepanes, but I'd use
> > something
> > like:
> >
> > With XLSheet
> >    .Activate
> >    .Cells.VerticalAlignment = xlTop
> >    .Range("a1").select  'make sure A1 is visible
> >    .Range("A2").Select
> >    XLApp.ActiveWindow.FreezePanes = True
> >    .Rows(1).Font.Bold = True
> >    .rows(1).autofilter
> >    .Cells(1, 1).Select
> > End With
> > XLBook.Save
> >
> > Lars Brownies wrote:
> >>
> >> Thanks Yawar, Dave,
> >> Works like a charm! See the (final) code below. Still, I have the feeling
> >> this code can be written more efficiently, especially the with statement.
> >>
> >> If you have any additional hints/tips, I'd love to hear them.
> >>
> >> Lars
> >>
> >> Private Sub btnEditExcel_Click()
> >> Dim XLApp As Excel.Application
> >> Dim XLBook As Excel.Workbook
> >> Dim XLSheet As Excel.Worksheet
> >>
> >> On Error GoTo btnEditExcel_Click_Error
> >>
> >> Set XLApp = New Excel.Application
> >> Set XLBook = XLApp.Workbooks.Open("c:\Map16.xls")
> >> Set XLSheet = XLBook.Worksheets(1)
> >>
> >> With XLSheet
> >>     .Activate
> >>     .Cells.VerticalAlignment = xlTop
> >>     .Range("A2").Select
> >>     XLApp.ActiveWindow.FreezePanes = True
> >>     .Rows("1:1").Select
> >>     XLApp.Selection.Font.Bold = True
> >>     XLApp.Selection.AutoFilter
> >>     .Cells(1, 1).Select
> >>     XLBook.Save
> >> End With
> >>
> >> Exit_this_sub:
> >>     Set XLSheet = Nothing
> >>     XLBook.Close
> >>     Set XLBook = Nothing
> >>     XLApp.Quit
> >>     Set XLApp = Nothing
> >>     Exit Sub
> >> btnEditExcel_Click_Error:
> >>    MsgBox "Error " & Err.Number & " :" & Err.Description
> >>    Resume Exit_this_sub
> >> End Sub
> >>
> >> "Dave Peterson" <petersod@verizonXSPAM.net> schreef in bericht
> >> news:49E23167.663BF388@verizonXSPAM.net...
> >> >I agree with Yawar's response, but you don't need to declare Selection.
> >> >
> >> > But would have to qualify it.
> >> >
> >> > With XLApp.Selection
> >> >
> >> > But it's better to drop the .select and just use:
> >> > with XLSheet.Cells
> >> >
> >> >
> >> >
> >> > Lars Brownies wrote:
> >> >>
> >> >> From Access I'm trying to set the vertical alignment for all cells of
> >> >> an
> >> >> Excelfile to 'Top':
> >> >>
> >> >> Dim XLApp As Excel.Application
> >> >> Dim XLBook As Excel.Workbook
> >> >> Dim XLSheet As Excel.Worksheet
> >> >>
> >> >> Set XLApp = New Excel.Application
> >> >> Set XLBook = XLApp.Workbooks.Open("c:\Test.xls")
> >> >> Set XLSheet = XLBook.Worksheets(1)
> >> >> XLSheet.Activate
> >> >> XLSheet.Cells.Select
> >> >>
> >> >> With Selection
> >> >>     .VerticalAlignment = xlTop
> >> >> End With
> >> >>
> >> >> XLBook.SaveAs ("c:\Test2.xls")
> >> >> Set XLSheet = Nothing
> >> >> XLBook.Close
> >> >> Set XLBook = Nothing
> >> >> XLApp.Quit
> >> >> Set XLApp = Nothing
> >> >>
> >> >> but I get the message objetvariable or blockvariable With is not set.
> >> >> .VerticalAlignment = xlTop
> >> >> is marked yellow.
> >> >>
> >> >> Can someone see from the code above what I'm doing wrong?
> >> >>
> >> >> Thanks,
> >> >>
> >> >> Lars
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
4/13/2009 5:32:00 PM
I see. Thanks again.

Lars

"Dave Peterson" <petersod@verizonXSPAM.net> schreef in bericht 
news:49E37710.7361BF5@verizonXSPAM.net...
> Try freezing the panes on a sheet/window that isn't active.
>
> Lars Brownies wrote:
>>
>> >> You need to activate the sheet to use the .freezepanes
>>
>> What exactly do you mean by this?
>>
>> Lars
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> schreef in bericht
>> news:49E26D45.940A62ED@verizonXSPAM.net...
>> > You need to activate the sheet to use the .freezepanes, but I'd use
>> > something
>> > like:
>> >
>> > With XLSheet
>> >    .Activate
>> >    .Cells.VerticalAlignment = xlTop
>> >    .Range("a1").select  'make sure A1 is visible
>> >    .Range("A2").Select
>> >    XLApp.ActiveWindow.FreezePanes = True
>> >    .Rows(1).Font.Bold = True
>> >    .rows(1).autofilter
>> >    .Cells(1, 1).Select
>> > End With
>> > XLBook.Save
>> >
>> > Lars Brownies wrote:
>> >>
>> >> Thanks Yawar, Dave,
>> >> Works like a charm! See the (final) code below. Still, I have the 
>> >> feeling
>> >> this code can be written more efficiently, especially the with 
>> >> statement.
>> >>
>> >> If you have any additional hints/tips, I'd love to hear them.
>> >>
>> >> Lars
>> >>
>> >> Private Sub btnEditExcel_Click()
>> >> Dim XLApp As Excel.Application
>> >> Dim XLBook As Excel.Workbook
>> >> Dim XLSheet As Excel.Worksheet
>> >>
>> >> On Error GoTo btnEditExcel_Click_Error
>> >>
>> >> Set XLApp = New Excel.Application
>> >> Set XLBook = XLApp.Workbooks.Open("c:\Map16.xls")
>> >> Set XLSheet = XLBook.Worksheets(1)
>> >>
>> >> With XLSheet
>> >>     .Activate
>> >>     .Cells.VerticalAlignment = xlTop
>> >>     .Range("A2").Select
>> >>     XLApp.ActiveWindow.FreezePanes = True
>> >>     .Rows("1:1").Select
>> >>     XLApp.Selection.Font.Bold = True
>> >>     XLApp.Selection.AutoFilter
>> >>     .Cells(1, 1).Select
>> >>     XLBook.Save
>> >> End With
>> >>
>> >> Exit_this_sub:
>> >>     Set XLSheet = Nothing
>> >>     XLBook.Close
>> >>     Set XLBook = Nothing
>> >>     XLApp.Quit
>> >>     Set XLApp = Nothing
>> >>     Exit Sub
>> >> btnEditExcel_Click_Error:
>> >>    MsgBox "Error " & Err.Number & " :" & Err.Description
>> >>    Resume Exit_this_sub
>> >> End Sub
>> >>
>> >> "Dave Peterson" <petersod@verizonXSPAM.net> schreef in bericht
>> >> news:49E23167.663BF388@verizonXSPAM.net...
>> >> >I agree with Yawar's response, but you don't need to declare 
>> >> >Selection.
>> >> >
>> >> > But would have to qualify it.
>> >> >
>> >> > With XLApp.Selection
>> >> >
>> >> > But it's better to drop the .select and just use:
>> >> > with XLSheet.Cells
>> >> >
>> >> >
>> >> >
>> >> > Lars Brownies wrote:
>> >> >>
>> >> >> From Access I'm trying to set the vertical alignment for all cells 
>> >> >> of
>> >> >> an
>> >> >> Excelfile to 'Top':
>> >> >>
>> >> >> Dim XLApp As Excel.Application
>> >> >> Dim XLBook As Excel.Workbook
>> >> >> Dim XLSheet As Excel.Worksheet
>> >> >>
>> >> >> Set XLApp = New Excel.Application
>> >> >> Set XLBook = XLApp.Workbooks.Open("c:\Test.xls")
>> >> >> Set XLSheet = XLBook.Worksheets(1)
>> >> >> XLSheet.Activate
>> >> >> XLSheet.Cells.Select
>> >> >>
>> >> >> With Selection
>> >> >>     .VerticalAlignment = xlTop
>> >> >> End With
>> >> >>
>> >> >> XLBook.SaveAs ("c:\Test2.xls")
>> >> >> Set XLSheet = Nothing
>> >> >> XLBook.Close
>> >> >> Set XLBook = Nothing
>> >> >> XLApp.Quit
>> >> >> Set XLApp = Nothing
>> >> >>
>> >> >> but I get the message objetvariable or blockvariable With is not 
>> >> >> set.
>> >> >> .VerticalAlignment = xlTop
>> >> >> is marked yellow.
>> >> >>
>> >> >> Can someone see from the code above what I'm doing wrong?
>> >> >>
>> >> >> Thanks,
>> >> >>
>> >> >> Lars
>> >> >
>> >> > --
>> >> >
>> >> > Dave Peterson
>> >
>> > --
>> >
>> > Dave Peterson
>
> -- 
>
> Dave Peterson 


0
Lars5404 (8)
4/13/2009 6:48:50 PM
That's one of the few things that you have to use .activate or .select, though. 
For the most part, you can work on things directly.

And when you write your code that way, it'll be easier to understand and update
later.

Lars Brownies wrote:
> 
> I see. Thanks again.
> 
> Lars
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> schreef in bericht
> news:49E37710.7361BF5@verizonXSPAM.net...
> > Try freezing the panes on a sheet/window that isn't active.
> >
> > Lars Brownies wrote:
> >>
> >> >> You need to activate the sheet to use the .freezepanes
> >>
> >> What exactly do you mean by this?
> >>
> >> Lars
> >>
> >> "Dave Peterson" <petersod@verizonXSPAM.net> schreef in bericht
> >> news:49E26D45.940A62ED@verizonXSPAM.net...
> >> > You need to activate the sheet to use the .freezepanes, but I'd use
> >> > something
> >> > like:
> >> >
> >> > With XLSheet
> >> >    .Activate
> >> >    .Cells.VerticalAlignment = xlTop
> >> >    .Range("a1").select  'make sure A1 is visible
> >> >    .Range("A2").Select
> >> >    XLApp.ActiveWindow.FreezePanes = True
> >> >    .Rows(1).Font.Bold = True
> >> >    .rows(1).autofilter
> >> >    .Cells(1, 1).Select
> >> > End With
> >> > XLBook.Save
> >> >
> >> > Lars Brownies wrote:
> >> >>
> >> >> Thanks Yawar, Dave,
> >> >> Works like a charm! See the (final) code below. Still, I have the
> >> >> feeling
> >> >> this code can be written more efficiently, especially the with
> >> >> statement.
> >> >>
> >> >> If you have any additional hints/tips, I'd love to hear them.
> >> >>
> >> >> Lars
> >> >>
> >> >> Private Sub btnEditExcel_Click()
> >> >> Dim XLApp As Excel.Application
> >> >> Dim XLBook As Excel.Workbook
> >> >> Dim XLSheet As Excel.Worksheet
> >> >>
> >> >> On Error GoTo btnEditExcel_Click_Error
> >> >>
> >> >> Set XLApp = New Excel.Application
> >> >> Set XLBook = XLApp.Workbooks.Open("c:\Map16.xls")
> >> >> Set XLSheet = XLBook.Worksheets(1)
> >> >>
> >> >> With XLSheet
> >> >>     .Activate
> >> >>     .Cells.VerticalAlignment = xlTop
> >> >>     .Range("A2").Select
> >> >>     XLApp.ActiveWindow.FreezePanes = True
> >> >>     .Rows("1:1").Select
> >> >>     XLApp.Selection.Font.Bold = True
> >> >>     XLApp.Selection.AutoFilter
> >> >>     .Cells(1, 1).Select
> >> >>     XLBook.Save
> >> >> End With
> >> >>
> >> >> Exit_this_sub:
> >> >>     Set XLSheet = Nothing
> >> >>     XLBook.Close
> >> >>     Set XLBook = Nothing
> >> >>     XLApp.Quit
> >> >>     Set XLApp = Nothing
> >> >>     Exit Sub
> >> >> btnEditExcel_Click_Error:
> >> >>    MsgBox "Error " & Err.Number & " :" & Err.Description
> >> >>    Resume Exit_this_sub
> >> >> End Sub
> >> >>
> >> >> "Dave Peterson" <petersod@verizonXSPAM.net> schreef in bericht
> >> >> news:49E23167.663BF388@verizonXSPAM.net...
> >> >> >I agree with Yawar's response, but you don't need to declare
> >> >> >Selection.
> >> >> >
> >> >> > But would have to qualify it.
> >> >> >
> >> >> > With XLApp.Selection
> >> >> >
> >> >> > But it's better to drop the .select and just use:
> >> >> > with XLSheet.Cells
> >> >> >
> >> >> >
> >> >> >
> >> >> > Lars Brownies wrote:
> >> >> >>
> >> >> >> From Access I'm trying to set the vertical alignment for all cells
> >> >> >> of
> >> >> >> an
> >> >> >> Excelfile to 'Top':
> >> >> >>
> >> >> >> Dim XLApp As Excel.Application
> >> >> >> Dim XLBook As Excel.Workbook
> >> >> >> Dim XLSheet As Excel.Worksheet
> >> >> >>
> >> >> >> Set XLApp = New Excel.Application
> >> >> >> Set XLBook = XLApp.Workbooks.Open("c:\Test.xls")
> >> >> >> Set XLSheet = XLBook.Worksheets(1)
> >> >> >> XLSheet.Activate
> >> >> >> XLSheet.Cells.Select
> >> >> >>
> >> >> >> With Selection
> >> >> >>     .VerticalAlignment = xlTop
> >> >> >> End With
> >> >> >>
> >> >> >> XLBook.SaveAs ("c:\Test2.xls")
> >> >> >> Set XLSheet = Nothing
> >> >> >> XLBook.Close
> >> >> >> Set XLBook = Nothing
> >> >> >> XLApp.Quit
> >> >> >> Set XLApp = Nothing
> >> >> >>
> >> >> >> but I get the message objetvariable or blockvariable With is not
> >> >> >> set.
> >> >> >> .VerticalAlignment = xlTop
> >> >> >> is marked yellow.
> >> >> >>
> >> >> >> Can someone see from the code above what I'm doing wrong?
> >> >> >>
> >> >> >> Thanks,
> >> >> >>
> >> >> >> Lars
> >> >> >
> >> >> > --
> >> >> >
> >> >> > Dave Peterson
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
4/13/2009 9:27:54 PM
Reply:

Similar Artilces:

Excel is sluggish
This seems to be a software issue with Excel and Word. When I went to the Microsoft site and entered my software serial number into their site, they said that because I had purchased the software through Gateway, that I had to ask Gateway for assistance. Gateway says to ask Microsoft. Go figure. Here is the problem. If I open a small document in Excel or Word and I am not connected to the Internet, it opens in about 1 second. If I try to open the same document and I am connected to the Internet, it takes about 25 seconds. This started happening within the past week. My system...

Excel Template Wizard
I need to linke a template to a data base with Template wizard. I have set up the wizard. I have a matrix of 6 column headings in the template with provision to add data in 20 rows below this column headings. The wizard is able to pick up the data only from first row with respect to 6 column headings. If I look up in the data base, one roq corresponding to the first record is saved. Can i do somthing so that for one template form, each of the 20 row data is saved in the data base. ...

Excel Functions #2
hi guys, just wondered if anyone knew which function i would use to do this, because im fairly novice with excel: Basically if the cell value is above 110 i want the cell to turn green if the cell value is between 110 and 105 i want it to turn yellow if the cell value is below 105 i want it to turn red any help would be seriously appreciated. thanks spotseven wrote: > hi guys, just wondered if anyone knew which function i would use to > do this, because im fairly novice with excel: > > Basically if the cell value is above 110 i want the cell to turn gr...

How do I delete a text box from an Excel spreadsheet?
Rightclick on it and select cut If the textbox is from the control toolbox toolbar, you may have to click on the designmode icon first. Uncle Binky wrote: -- Dave Peterson ...

Awesome list of Excel/Word/Powerpoint shortcuts!
Hi, everybody! I recently discovered a great keyboard shortcut list for the office applications. It seems to have practically every shortcut imaginable and it fits on one page too! I printed it out and put on the wall next to my monitor -- just great!. Thought you would appreciate it... Here's the link to the PDF file. Hit print and you're ready to go. http://www.prihodko.com/msoffice/Office_2003_Shortcuts.pdf Lindy Hi Lindy, For Excel, I like Chip Pearson's Excel Keyboard Shortcuts page at: http://www.cpearson.com/excel/KeyboardShortcuts.htm There is also a d...

How do I set up inches per Minute/Second in Excel
I need to know how to take a known inch per Minute and divide by a known measurement and displayed as minutes and seconds. The answer of the equation (minutes and seconds) is where I'm having trouble. Actually, you need to divide your measured length by the known rate (inches per minute). This gives you the number of minutes, but to display using m:ss format you need to divide by 1440, the number of minutes per day (since Excel counts time as the fraction of a day). So put your measurement in A2, your rate in B2, and this formula into C2: =A2/B2/1440 Then format C2 using a custom...

Using insert to paste a row--how done in Excel 2007
Hi, In my old version of Excel, I could copy a row or chunk of rows, move to a new spot and use the "insert row" icon to insert the rows and paste it automatically. Now in Office 2007 it just inserts a row instead of what I have copied. I want it the old way! How do I do it? -- Thanks, PTweety R-click, Insert Copied Cells. pickytweety wrote: > Hi, > In my old version of Excel, I could copy a row or chunk of rows, move to a > new spot and use the "insert row" icon to insert the rows and paste it > automatically. Now in Office 2007 it just inserts a r...

Can I make a database in Word with Excel Data
I know this may be a "DUH" question, but... I am making a list of area dentists that I have copied and pasted of of the Dex website into Excel. I am wondering if I can take this inf and make a database in Word for printing address labels. Does anyon know if this can be done, and HOW?:confused -- Myn77 ----------------------------------------------------------------------- Myn777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2958 View this thread: http://www.excelforum.com/showthread.php?threadid=49283 For help on Word mail merge using Excel as the...

clicking TOOLS/OPTIONS gives me error msg "cannot access dir D:/"
I need to change settings in the TOOLS/OPTIONS menu. But when I click on any tab in the OPTIONS menu it gives me the error message "cannot access dir D:/documents" I may have changed the name of that directory at some point and I suspect that is the problem. However I still need to change settings in the TOOLS/OPTIONS menu. How do I fix it? Hi Lori Close Excel Add this directory D:/documents and try again And try again -- Regards Ron de Bruin http://www.rondebruin.nl "Lori Rossi" <Lori Rossi@discussions.microsoft.com> wrote in message news:34A25E1A-D...

Saving Excel workbook in SQL server using c#
Could anyone please help me out as to how we can save the excel workbook in the database and read it back. I was able to convert the text files and image files into binary format and save them to the DB and finally able to retrive them back in the same format. But was unable to do same for the excel object. Your help will be greatly appreciated. Thanks, regards, jitender ...

tab in access
-- I added a tab control to a form what do i do so it should be from right to left? Gal On Sun, 21 Mar 2010 02:57:01 -0700, Gal <Gal@discussions.microsoft.com> wrote: That is currently not supported. Why do you need it? -Tom. Microsoft Access MVP > >-- I added a tab control to a form what do i do so it should be from right >to left? >Gal I am programing for a company in Israel and they write in hebrew plz help me if u can -- Gal "Gal" wrote: > > -- I added a tab control to a form what do i do so it should be from right ...

Outlook failure to send Access reports
I email reports from Access using Outlook. At times, the email will hang up in my outbox and will not send even if I open it and try to send it manually. Any of you fine folks know what's going on and how to fix this? Thanks - Ken Smith -- KMS Technical Solutions ...

Create form similar to MS Access 2000 Help
Is it possible to create a form that works similar to the Help - Index form in Access 2000? My StreetTable consists of the following fields: Street_ID(autonumber), StreetName(text), Directions(memo). I want to be able to type the beginning letters of the street and the List of StreetNames continually reduces until I see the street I need in the StreetNames window. I would then click on that street and the Directions would appear in the third window panel. Since I'm dealing with approx. 5000 streets, scrolling really isn't an option that I want to consider. I found that a combo ...

Access ODBC problem: Excel cannot get float columns
I need some help I'm trying to get Access external data from an Excel workbook, using the MS Query feature. Everything is ok except when I try to fetch some tables that ODBC refuses to get data from. The error message tells that the MS Access ODBC driver doesn't allow some columns to be transmited because of its number of characters. The most strange thing is that I can see the data from MS Query correctly, but I caannot get it back from Excel. After some tries, I thing it occurs only with real typed columns. Can anybody help me? I use Windows XP Home + Office 2000 spanish versio...

2003 excel
I would like to change the cell color if there is a "r" found in it. All the cells are in the same colum and there are also numbers in the cells with the "r". Can anyone help ? Assume data in col A Select the col, then apply conditional formatting using Formula Is: =ISNUMBER(SEARCH("r",A1)) Format to taste > OK out Replace SEARCH with FIND if you need it to be case sensitive -- Max Singapore http://savefile.com/projects/236895 Downloads:16,400 Files:356 Subscribers:53 xdemechanik --- "Dave" wrote: > I would like to change the cell color if ...

Sending emailes from Access
Once i week i would like an easy way from access to send out a a email to some customer that query will find. Whats the eaasyest wasy to send a email to about 20- 30 people, the email will be the same to every one, I know i could go throught outlook and do a mail merge but is there a way i can just have a button on a access form that will automatical send out an email to lots of people Thanks very much Any help would be great Best Regards Simon Dickson Poke around the Access EMail FAQ that Tony Toews has at http://www.granite.ab.ca/access/email.htm. There's likely something there...

Outlook Web Access error
Got this error message when trying to access my mailbox from Outlook Web Access. "Unable to get renderer" This happened on a particular machine but work fine on other machine. Anyone know what going on? ...

HELP: Excel userform version control issue
Hi All, I have an excel userform that is posted on my companys intranet. The issue I am having is, users are saving this form to their hard drive and then using it(Instead of launching it everytime from intranet), SO basically when I have a new version of form on intranet they still use the old version as they have a habbit of saving it on their hard drive. Is there a way to resove this? something like they would see a popup message saying that "This is an older version that you are using, Please launch the form from Intranet for newer version". Thanks in advan...

Access Reports Disappear
I had a database that I had used for years with no problem. Lately I've noticed that some of the reports are disappearing. Yet, if I try to create a new report with the same name, Access says "Name conflicts with existing module, project or object library." So, I created a new database. The only thing I imported was the tables from the old database. Yet, I'm having the same problem. Has anyone seen this before? Make sure you have SHOW HIDDEN OBJECTS set to YES. Then take a look around for those "missing" reports... "JG" wrote: > I had a da...

Error in code exportin tables to excel
I'm tryin to export a table to excel with the code: Private Sub Command4_Click() On Error GoTo Err_Command0_Click Dim stDocName As String stDocName = "F01_UT" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, F01_UT, "C:\TRY\Tbls", True Exit_Command0_Click: Exit Sub Err_Command0_Click: MsgBox Err.Description Resume Exit_Command0_Click End Sub However, Access displays the message: Compile error : Syntax error What I'm doing wrong? If I want to export more tablas do I have to write more lines (DoCmd.TransferSpreadsheet acExport, acSpr...

How to add summary fields to Group Footer in Access Reports?
How do you add a Summary fields to Group Footers in Access? I have a Detail field I want to Sum in the Group Footer in my report. -- Pat Dools ...

Excel's localization of scientific notation
Does Excel use "E+" and "E-" for scientific notation for all locales? Or are there some locales for which it uses different characters? Thanks, Greg I'm pretty sure that the E is universal. If it were not, it would have been in the list of international symbols. Look here for more info about international issues: http://www.rondebruin.nl/international.htm#strings -- Kind regards, Niek Otten Microsoft MVP - Excel "Greg Lovern" <gregl@gregl.net> wrote in message news:82e5afbe-a4ba-4f5f-ac41-ac7b41519d67@h40g2000prf.googl...

How do I alpha my mailing list in Microsoft excel and delete names
To alphabetize -- use sort Sorting, Some notes on sorting in Excel http://www.mvps.org/dmcritchie/excel/sorting.htm To delete rows pick a range of rows by selecting one or more contiguous row number(s) then Edit, Delete rows You might also be interested in Filtering to expose only names in Ohio You can use Filtering to permanently delete rows or just hide them. you can read more about filtering at http://www.contextures.com/tiptech.html If you want to print labels for your mailing list, you do the printing in MS Word with Excel acting as your data bas...

emailing spreadsheet within excel
I have a user that has sent her spreadsheet as an attachment using the Send To Function. We use Outlook. Now, it seems as if the Send To option has lost its settings and is now trying to set up an Internet account instead of using the Microsoft Exchange Settings. Anyone know how to reset the Send To settings in Excel? Thanks! ...

How do I import data from a SECURED website into Excel?
I can import data from a regular web page directly into Excel spreadsheet, but when I tried importing them from a secured site I encountered problems. Does anyone have any suggestions? ...