Re: Selecting Used Cells In a Column... by D
D
Thu Jul 13 17:04:25 CDT 2006
Hi,
Here's some real cludgy code that I use to convert a csv file to xls.
You'll see that it uses "lo_excel.ActiveSheet.UsedRange.Select" before
autofitting and autofiltering columns. The code is a bit of mess, and I
hope I'm not overloading you with unnecessary cludge. I found working Excel
quite tricky, and I'm a very long way off from being good with it... anyway,
take what you want/can from the code. I've posted here, in the hope that
you might want to debug it, take it apart and see how it works.
Watch out for line/word wrap.
Towards the end of the routine, I was experimenting with temporarily
changing the default printer to a one called "generic". On my system I
created a printer called "generic", which uses the simple text only printer
driver. I do this because when using the "pagination" type stuff in Excel,
it is VERY slow when the default printer is a complex colour printer. I've
no idea why, I just know that it's much faster if the default printer
happens to be something simple.
Regards,
Dave.
This sruff is defined globally;
Dim go_fso, go_net
Set go_fso = CreateObject( "Scripting.FileSystemObject" )
Set go_net = CreateObject( "WScript.Network" )
And I use other routines called s_log() and s_error() - which you'll have to
write for yourself...
Sub s_csv_to_xls( ps_csv_spec, ps_xls_spec, ps_print_area )
Const cs_fac = "%s_csv_to_xls, "
Const cb_debug = False
Const xlCalculationManual = -4135
Const xlCalculationAutomatic = -4105
Const xlPortrait = 1
Const xlLandscape = 2
Const xlLeft = -4131
Const xlRight = -4152 '(&HFFFFEFC8)
Dim lo_csv_file, lo_excel, lo_book, lo_sheet
Call s_log( cs_fac & "Converting `" & ps_csv_spec & "` to `" & ps_xls_spec
& "`..." )
If cb_debug Then Call s_log( cs_fac & "Opening file `" & ps_csv_spec &
"`..." )
Set lo_csv_file = go_fso.GetFile( ps_csv_spec )
Set lo_excel = CreateObject( "Excel.Application" )
lo_excel.WorkBooks.Open lo_csv_file
Set lo_book = lo_excel.WorkBooks(1)
Set lo_sheet = lo_book.WorkSheets(1)
lo_excel.ScreenUpdating = False
lo_excel.Calculation = xlCalculationManual 'A book has to be open to
set/change calculation.
lo_sheet.DisplayPageBreaks = False
If go_fso.FileExists( ps_xls_spec ) Then
On Error Resume Next
go_fso.DeleteFile ps_xls_spec
If Err.Number <> 0 Then Call s_error( cs_fac & "Unable to delete file `"
& ps_xls_spec & "`..." )
On Error Goto 0
End If
If cb_debug Then Call s_log( cs_fac & "Text to columns..." )
'expression.TextToColumns( Destination, DataType, TextQualifier,
ConsecutiveDelimiter,
' Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo,
DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers)
lo_excel.Selection.TextToColumns
If cb_debug Then Call s_log( cs_fac & "Formatting..." )
' lo_sheet.Range("B1","P1").HorizontalAlignment = xlRight 'Align right the
header.
' lo_sheet.Columns("D:D").NumberFormat = "[h]:mm:ss"
' lo_sheet.Columns("E:F").NumberFormat = "#,##0"
' lo_sheet.Columns("M:M").NumberFormat = "#,##0"
lo_excel.ActiveSheet.UsedRange.Select 'Select all used cells.
lo_excel.Selection.Columns.AutoFit 'Autofit the selection.
lo_excel.Selection.Columns.AutoFilter 1 'Filter data.
lo_excel.ActiveSheet.Cells(1,1).Select 'Position to top left.
lo_excel.Windows(1).SplitRow = 1
lo_excel.Windows(1).SplitColumn = 2
lo_excel.Windows(1).FreezePanes = True
' lo_sheet.Range("A:D").ColumnWidth = 20
' lo_sheet.Range("G:I").ColumnWidth = 30
If ps_print_area = "$A:$P" Then lo_sheet.Range("A:P").ColumnWidth = 20
If cb_debug Then Call s_log( "" )
If cb_debug Then Call s_log( cs_fac & "Page setup, timer starts now..." )
Const cb_use_generic_printer_if_exists = True
Dim ld_run_start, ld_run_end
ld_run_start = Now
Const cs_generic_printer = "Generic"
Dim ls_default_printer, lo_printers, ll_printer,
lb_generic_printer_exists, lo_wmi, lc_printers, lo_printer
ls_default_printer = ""
If cb_use_generic_printer_if_exists Then
lb_generic_printer_exists = False
Set lo_printers = go_net.EnumPrinterConnections
For ll_printer = 0 To lo_printers.Count - 1 Step 2
If cb_debug Then Call s_log( cs_fac & "Found printer `" &
lo_printers.Item(ll_printer) & "` named `" & lo_printers.Item(ll_printer+1)
& "`..." )
If lo_printers.Item(ll_printer+1) = cs_generic_printer Then
lb_generic_printer_exists = True
Next
Set lo_printers = Nothing
If lb_generic_printer_exists Then
Set lo_wmi = GetObject( "WinMgmts:\\.\root\cimv2" )
Set lc_printers = lo_wmi.ExecQuery( "Select Attributes,Caption From
Win32_Printer" )
For Each lo_printer In lc_printers
If lo_printer.Attributes And 4 Then
ls_default_printer = lo_printer.Caption
End If
Next
If cb_debug Then Call s_log( cs_fac & "Found default printer of `" &
ls_default_printer & "`..." )
If cb_debug Then Call s_log( cs_fac & "Setting printer `" &
cs_generic_printer & "` as the default printer..." )
go_net.SetDefaultPrinter( cs_generic_printer )
If cb_debug Then Call s_log( cs_fac & "Default printer now set to `" &
cs_generic_printer & "`..." )
Else
If cb_debug Then Call s_log( cs_fac & "Didn't find a generic printer
called `" & cs_generic_printer & "`, the pagination may be slow..." )
End If
Else
If cb_debug Then Call s_log( cs_fac & "Not attempting to use `" &
cs_generic_printer & "` printer, pagination may be slow..." )
End If
If cb_debug Then Call s_log( "<with>" )
With lo_excel.ActiveSheet.PageSetup
If cb_debug Then Call s_log( "PrintArea" )
.PrintArea = ps_print_area 'e.g. "$A:$P"
If cb_debug Then Call s_log( "PrintTitleRows" )
.PrintTitleRows = "$1:$1"
If cb_debug Then Call s_log( "PrintTitleColumns" )
.PrintTitleColumns = ""
If cb_debug Then Call s_log( "LeftHeader" )
.LeftHeader = "Date: &D &T"
If cb_debug Then Call s_log( "CenterHeader" )
.CenterHeader = "File: &F"
If cb_debug Then Call s_log( "RightHeader" )
.RightHeader = "Page &P of &N"
If cb_debug Then Call s_log( "LeftFooter" )
.LeftFooter = ""
If cb_debug Then Call s_log( "CenterFooter" )
.CenterFooter = ""
If cb_debug Then Call s_log( "RightFooter" )
.RightFooter = ""
If cb_debug Then Call s_log( "LeftMargin" )
.LeftMargin = 0
If cb_debug Then Call s_log( "RightMargin" )
.RightMargin = 0
If cb_debug Then Call s_log( "TopMargin" )
.TopMargin = 30
If cb_debug Then Call s_log( "BottomMargin" )
.BottomMargin = 0
If cb_debug Then Call s_log( "HeaderMargin" )
.HeaderMargin = 0
If cb_debug Then Call s_log( "FooterMargin" )
.FooterMargin = 0
If cb_debug Then Call s_log( "Orientation" )
If ps_print_area = "$A:$P" Then
.Orientation = xlLandscape
Else
.Orientation = xlPortrait
End If
If cb_debug Then Call s_log( "Zoom" )
.Zoom = False
If cb_debug Then Call s_log( "FitToPagesWide" )
.FitToPagesWide = 1
If cb_debug Then Call s_log( "FitToPagesTall" )
.FitToPagesTall = 99
If cb_debug Then Call s_log( "done" )
End With
If cb_debug Then Call s_log( "<end with>" )
If cb_use_generic_printer_if_exists Then
If ls_default_printer <> "" Then
If cb_debug Then Call s_log( cs_fac & "Resetting default printer to `"
& ls_default_printer & "`..." )
go_net.SetDefaultPrinter( ls_default_printer )
If cb_debug Then Call s_log( cs_fac & "Default printer has been reset
to `" & ls_default_printer & "`..." )
End If
End If
ld_run_end = Now
If cb_debug Then Call s_log( cs_fac & "Pagination duration `" & Trim(
fs_duration( ld_run_start, ld_run_end ) ) & "`..." )
If cb_debug Then Call s_log( "" )
If cb_debug Then Call s_log( cs_fac & "Saving..." )
On Error Resume Next
lo_book.SaveAs ps_xls_spec, True
If Err.Number <> 0 Then Call s_error( cs_fac & "Failed to save Excel file
`" & ps_xls_spec & "`." )
On Error Goto 0
If cb_debug Then Call s_log( cs_fac & "Closing..." )
lo_excel.Calculation = xlCalculationAutomatic 'A book has to be open to
set/change calculation.
lo_excel.ScreenUpdating = true
lo_book.Close True
Set lo_sheet = Nothing
Set lo_book = Nothing
Set lo_excel = Nothing
Set lo_csv_file = Nothing
End Sub
"tbader01" <tbader01@discussions.microsoft.com> wrote in message
news:93F9069B-F8EB-4593-AA2C-B962BF694661@microsoft.com...
> Hello,
> I have been messing with this for a while, and decided I need professional
> help!
> I would like to select the used cells in a column.
> I I can do it by being at the top (or bottom) of the column, then using
> CTRL+SHIFT+Up (or Down) arrow to highlight the range, but that seems sort
> of
> cludgy to me!
> It seems that the .UsedRange command is the way to go, but I can't figure
> out what needs to go before and/or after it to only select that columns
> used
> range of cells!
>
> Any help would be appricated!
>