Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

VBA-Excel (column Justification)

How do I force a justificaton (left,center,right) for an entire column in Excel from VB ?


View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Column Justification
Hi have just finished designing an application where I now need to add
a printing routine eg print to paper and print to PDF. All works well but I need to right justify some tabs mainly for currency reports(right justify) but include text on the same report(Left Justify). any thoughts without using word.

Column Justification
Hi, I have just completed an application, and now need to add a printing
routine, (print to paper and PDF). All works well. except I am having proplems trying to right justify tabs. The print routine needs to print Text and currency in columns (Tabular). Thats fine, the text is left justified, but currency need to be right justified, how do I change tab justifation on each tab sent to the printer (PDF or paper) via vb6 ??? Can anyone help

Is There Anyway To Set Justification For A Column In Listview?
Is there anyway to set justification for a column in listview?

DataGrid Column Justification
Hi All,

Anybody know how do I justify the column header to the right in aDataGrid Control?


Listview Column Justification
Is there a way to programatically right-justify a column in a listview? I have a dollar amount that I want to right-justify (it's a string value) and can't seem to find the right property or function. Any help would be appreciated!


How To Convert Scientific Value Of Excel Sheet Column To Number Column In Vb?
Hai Experts,
I have two columns in excel sheet with scientific value (Eg:5E+11).I want to convert this value into number --like 500000(not specifically this no this is an example) using visual basic

Can anyone of u tell me how to do this conversion process?


Dynamically Selecting Column By Excel Column Header (B, C... AD, AE, Etc...)
Hi Guys,

I am trying to automate a spreadsheet which would copy values from one worksheet, and insert it into another after manipulating the values slightly. I have managed most of the code (as shown below), however, I am struggling with the following:

The spreadsheet contains pricing information per supplier, and my aim is to retrieve the part number, size, and cost per part from the original into a new worksheet. As the cost column may be in a different location (based on multiple suppliers in the same worksheet), i need to prompt the user for the worksheet that contains the information and the column that contains the costs. I have already written the code to parse the worksheets and populate the combo box with worksheet names. Once a worksheet is selected, I need to parse all used columns and populate a combo box with the Excel column headers (e.g. B, C... AX, BE, etc...) so that the user can choose the correct column. Once the column is selected, I need to convert the column header to a column number (e.g. Col A = 1, Col F=6, Col BC = 46) so that I can copy the values via the GetRows() subroutine in the code.

Any help would be appreciated. Included below is the code that I have thusfar...



Private Sub Worksheet_Activate()

' Get number of worksheets in workbook

' Get names of worksheets in workbook
For iCnt = 1 To ActiveWorkbook.Worksheets.Count
    If ActiveWorkbook.Sheets(iCnt).Name <> "Data Unload" Then
        cboWorksheet.AddItem ActiveWorkbook.Sheets(iCnt).Name
    End If

End Sub

Private Sub GetRows()

' Setup variables to be used
Dim strNewPart, strNewSize, strSheetName As String
Dim iRowData, sRowData, x, y As Integer

strSheetName = cboWorksheet.Text
For i = 1 To ActiveWorkbook.Worksheets.Count
    If ActiveWorkbook.Sheets(i).Name = strSheetName Then
        x = i
    End If
    If ActiveWorkbook.Sheets(i).Name = "Data Unload" Then
        y = i
    End If
'Setup Variables in Use
iLastRow = ActiveWorkbook.Sheets(x).Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row
sRowData = 6
Debug.Print "iLastRow = " & iLastRow
Debug.Print "sRowData = " & sRowData

       ' Loop through the data table and compare each record with data in the lookup table
       ' If a match is found, change the data table with what is in the lookup table
       For iRowData = 8 To iLastRow
           If Worksheets(x).Cells(iRowData, 1).Value <> "" Then
              If Worksheets(x).Name = "Product X" Then
                  strNewPart = Worksheets(x).Cells(iRowData, 1).Value & "X"
                  strNewSize = "0.0"
                  strNewPart = Worksheets(x).Cells(iRowData, 1).Value & "Z"
                  strNewSize = "0.0"
              End If
              Worksheets(y).Cells(sRowData, 1).Value = strNewPart
              Worksheets(y).Cells(sRowData, 2).Value = strNewSize
           End If
       'increment Unload Counter
       sRowData = sRowData + 1



End Sub

Excel - Return Column Alphabet From Column Number
Is there some function in Excel (VBA) that will get me the column alphabet if I provide the column number, or do I have to write one. I ask because I know that there is, yet I recall I wrote this functionality for the previous application, and I don't want to:

(1) Reuse it, if there's something in-built
(2) Re-invent the wheel


PS: I'm looking for something like:


Function GetAlphabet(ByVal ColumnNumber as Long) as String

which if I call must say:



Output: "A"

How do I right-justify columns of numbers when printing
in VB.NET ? Need code or reference - thanks -sidb

I have placed a label or text box on a form and set the text box to multiline property to true. I have entered the text and I want it should be justified. Please help in this regard.



How To Shade Cell In Excel Based On The Date Value In The Excel Column
Hai ,

I export the content of the MSHFlexgrid to ExcelSheet , Here with i have attached my Excel sheet. The below is the Code i use to export the Excel Sheet. If you see Excel Sheet attached you can find the cells shaded. The code is working fine,

I need a alteration in it. I need to shade the Cell based on the Value in the Column DOffStrm,

if the value in that column is below 30 and the T & I Ets/Ospas date begins with 1 (ex: 3/01/2007)
then the shade should be inside one cell,

suppose the Column DOffStrm is 30 and the Date value of T & I Ets/Ospas is 3/15/2007 then shade should start from half of the current cell and end in the second half of the next cell.

Kindly check this and tell me.


VB Code:
Private Sub cmdExport_Click() Dim MDur As Integer Dim obj1 As New Excel.Application Dim wsheet As Worksheet Dim wbook As Workbook  Screen.MousePointer = vbHourglass  Set wbook = obj1.Workbooks.Add Set wsheet = obj1.Sheets(1) Dim i% Dim j% Dim Ce%  For i = 0 To MSHFlexGrid1.Rows - 1     For j = 0 To MSHFlexGrid1.Cols - 1                     If j > 7 And j < 55 And i > 1 And Len(MSHFlexGrid1.TextMatrix(i, j)) > 1 Then             MDur = MSHFlexGrid1.TextMatrix(i, 55)             MDur = MDur / 30             wsheet.Cells(i + 2, j + 1).Value = Format(MSHFlexGrid1.TextMatrix(i, j), "dd")             wsheet.Cells(i + 2, j + 1).Font.Bold = True             obj1.ActiveSheet.Cells(i, j).Font.Color = vbRed             obj1.ActiveSheet.Cells(i + 2, j + 1).Interior.Color = vbYellow             For Ce = 0 To MDur                              Next         ElseIf j > 7 And j < 55 Then             wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)             wsheet.Cells(i + 2, j + 1).Font.Bold = True         Else             wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)         End If     Next Next  For i = 0 To 1     For j = 0 To MSHFlexGrid1.Cols - 1         wsheet.Cells(i + 2, j + 1).Font.Bold = True         wsheet.Cells(i + 2, j + 1).Font.Color = &H800000     Next Next Application.DisplayAlerts = False With obj1.Sheets(1).Range("H2:S2")     .Select     .Merge End With  With obj1.Sheets(1).Range("T2:AE2")     .Select     .Merge End With  With obj1.Sheets(1).Range("AF2:AQ2")     .Select     .Merge End With  With obj1.Sheets(1).Range("AR2:BC2")     .Select     .Merge End With obj1.Rows(2).HorizontalAlignment = Excel.xlCenter obj1.Columns.AutoFit Screen.MousePointer = vbNormal obj1.Application.Visible = True End Sub

also attached another excel file call Sheet, this type of shading is required here as in the Book1 which is attached here,

so i have can have the copy of the same picture in 4 different Width,

1st picture width will be of 24 and
2nd will be 50,
3rd will be 75
4th will be 100

so with this 4 different images i can cover the area in the cell,

the only code now needed is

1. how to place the Picture in the ExcelSheet through VB.6

2. how to specify the starting part (or X axis or Cell Range in Excel Sheet) of the picture to be placed

i hope, if i have the code for the above two, then it will be solved. Kindly check this and reply me.


How To Shade Cell In Excel Based On The Date Value In The Excel Column
Hai ,

 I export the content of the MSHFlexgrid to ExcelSheet , Here with i have attached my Excel sheet. The below is the Code i use to export the Excel Sheet. If you see Excel Sheet attached you can find the cells shaded. The code is working fine,

I need a alteration in it. I need to shade the Cell based on the Value in the Column DOffStrm,

if the value in that column is below 30 and the T & I Ets/Ospas date begins with 1 (ex: 3/01/2007)
then the shade should be inside one cell,

suppose the Column DOffStrm is 30 and the Date value of T & I Ets/Ospas is 3/15/2007 then shade should start from half of the current cell and end in the second half of the next cell.

Kindly check this and tell me.

Code:Private Sub cmdExport_Click()
Dim MDur As Integer
Dim obj1 As New Excel.Application
Dim wsheet As Worksheet
Dim wbook As Workbook

Screen.MousePointer = vbHourglass

Set wbook = obj1.Workbooks.Add
Set wsheet = obj1.Sheets(1)
Dim i%
Dim j%
Dim Ce%

For i = 0 To MSHFlexGrid1.Rows - 1
    For j = 0 To MSHFlexGrid1.Cols - 1
        If j > 7 And j < 55 And i > 1 And Len(MSHFlexGrid1.TextMatrix(i, j)) > 1 Then
            MDur = MSHFlexGrid1.TextMatrix(i, 55)
            MDur = MDur / 30
            wsheet.Cells(i + 2, j + 1).Value = Format(MSHFlexGrid1.TextMatrix(i, j), "dd")
            wsheet.Cells(i + 2, j + 1).Font.Bold = True
            obj1.ActiveSheet.Cells(i, j).Font.Color = vbRed
            obj1.ActiveSheet.Cells(i + 2, j + 1).Interior.Color = vbYellow
            For Ce = 0 To MDur
        ElseIf j > 7 And j < 55 Then
            wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)
            wsheet.Cells(i + 2, j + 1).Font.Bold = True
            wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)
        End If

For i = 0 To 1
    For j = 0 To MSHFlexGrid1.Cols - 1
        wsheet.Cells(i + 2, j + 1).Font.Bold = True
        wsheet.Cells(i + 2, j + 1).Font.Color = &H800000
Application.DisplayAlerts = False
With obj1.Sheets(1).Range("H2:S2")
End With

With obj1.Sheets(1).Range("T2:AE2")
End With

With obj1.Sheets(1).Range("AF2:AQ2")
End With

With obj1.Sheets(1).Range("AR2:BC2")
End With
obj1.Rows(2).HorizontalAlignment = Excel.xlCenter
Screen.MousePointer = vbNormal
obj1.Application.Visible = True
End Sub


Vb/Excel: How To Read An Excel File In A Column WITHOUT Specifying The Range ??
Hello, all.

I found a bit of code that lets me read an Excel file from and to a specifi
range. See the code below. But what if I only want to specify the STARTING
position and simply want it to continue reading down the column until it
hits an empty cell?

Does anyone know how to do this? Thanks!

Here is the range-only code:

' ** turn screen updating off, prevent the user from seeing the source
' ** then find the file and identify both the worksheet and range
Application.ScreenUpdating = False
Set sourcewb = Workbooks.Open(file, False, True)
listitems = sourcewb.Worksheets(sheet).range(temp_range).Value

' ** close the source workbook without saving changes, then reactive
screen update
sourcewb.Close False
Set sourcewb = Nothing
Application.ScreenUpdating = True

' ** read the data, convert values to a vertical array
listitems = Application.WorksheetFunction.Transpose(listitems)

For count = 1 To UBound(listitems)
' ** read the info into a temporary field
temp_var(count) = listitems(count) ' ** populate the array
Next count

' ** no items selected, set to 0 to select the first item
'Combo_machine.ListIndex = -1

Close Workbook

Easy Right Justification
in a printed report you cant right justify number from the format menu unless a '0' leads - does anyone know the code / procedure to alow right justification without needing a leading 0?

Flexgrid Justification
Is it possible to justify each column separately in a flexgrid? I need both left, centre and right.


Different Justification Question
I have a form that has lots of labels and text boxes. It actually looks similar to the jeopardy game show board. Anyway, I would like them to always be in the center of the screen. I have the forms Window State property set to
Maximized, but with different monitor screen sizes the text boxes and labels are not always centered. Is there anyway to insure they will be always be centered no matter what size monitor the user has. Or am I spitting in the wind.



Listview Justification
Can somebody tell me if it possible to have different columns in a listview with different justifications.


Column 1 is for Surnames so it should be left justified.
Column 2 is for Wage so it should be right justified.

Is it possible?

How To Set Justification Of MSFlexGrid

How can I set the left justification to my MSFlxGrid data so that it will not slow down the creation of list.

Do While i <= .RecordCount
            frmMain.MSFlexGrid1.TextMatrix(i, 0) = .Fields(0).Value
            frmMain.MSFlexGrid1.TextMatrix(i, 1) = .Fields(1).Value
            frmMain.MSFlexGrid1.TextMatrix(i, 2) = .Fields(2).Value
            frmMain.MSFlexGrid1.TextMatrix(i, 3) = .Fields(3).Value
            frmMain.MSFlexGrid1.TextMatrix(i, 5) = .Fields(4).Value
            frmMain.MSFlexGrid1.TextMatrix(i, 6) = .Fields(5).Value
            frmMain.MSFlexGrid1.Row = i
            For t = 0 To frmMain.MSFlexGrid1.Cols - 1
                frmMain.MSFlexGrid1.Col = t
                frmMain.MSFlexGrid1.CellAlignment = flexAlignLeftCenter
            Next t

This slows down the creation of data list, and if I leave the loop where it sets flexAlignLeftCenter and one of the columns is just plain numbers, it will force it all the way to the right of the cell while String values go left.

Thanks for your help!

Multiline Justification In VBA ???
Hi I'm french and I try to find a code wich attribut the justification of a Multiline selected to a variable.
See this code, you will understand. I just need a world.

Dim elem As AcadObject
Dim retCoord As Variant
Dim justification as variant
Dim intN As Long
Dim strMsg As String
Dim strObjectName As String

For Each elem In ThisDrawing.ModelSpace ' Boucle sur les entités de l'espace objet

strObjectName = elem.ObjectName
strMsg = ""

Case Is = "AcDbMline"
retCoord = elem.Coordinates
justification = elem.?????????????????
For intN = LBound(retCoord) To UBound(retCoord) Step 3
strMsg = strMsg & "x: " & retCoord(intN) & " y: " & retCoord(intN + 1) & " z: " & retCoord(intN + 2) & vbCrLf
MsgBox strMsg, vbInformation, "Coordinate of the muliline " & elem.Handle

End Select

Thank you very much


RichTextBox Justification Not Apparent

I've been testing the following sample code for adding full justification to a RichTextBox control (Window XP):

Private Declare Function SendMessageLong Lib "user32" Alias "SendMessageA" (ByVal HWND As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal HWND As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Const WM_USER = &H400
Private Const PFA_LEFT = 1
Private Const PFA_RIGHT = 2
Private Const PFA_CENTER = 3
Private Const PFA_JUSTIFY = &H4
Const MAX_TAB_STOPS = 32
Private Type PARAFORMAT2
cbSize As Long
dwMask As Long
wNumbering As Integer
wEffects As Integer
dxStartIndent As Long
dxRightIndent As Long
dxOffset As Long
wAlignment As Integer
cTabCount As Integer
rgxTabs(MAX_TAB_STOPS - 1) As Long
dySpaceBefore As Long
dySpaceAfter As Long
dyLineSpacing As Long
sStyle As Integer
bLineSpacingRule As Byte
bOutlineLevel As Byte
wShadingWeight As Integer
wShadingStyle As Integer
wNumberingStart As Integer
wNumberingStyle As Integer
wNumberingTab As Integer
wBorderSpace As Integer
wBorderWidth As Integer
wBorders As Integer
End Type
Public Enum ERECParagraphAlignmentConstants
ercParaLeft = PFA_LEFT
ercParaCentre = PFA_CENTER
ercParaRight = PFA_RIGHT
ercParaJustify = PFA_JUSTIFY
End Enum
Private Const PFM_ALIGNMENT = &H8&

Private Function SetAlignment(lHwnd As Long, ByVal eAlign As ERECParagraphAlignmentConstants)
Dim lR As Long
tP2.cbSize = Len(tP2)
tP2.wAlignment = eAlign
lR = SendMessage(lHwnd, EM_SETPARAFORMAT, 0, tP2)
End Function
When editing text in the RTB control, and using Call SetAlignment(Me.RichTextBox1.hWnd, ercParaJustify) the text does *not* appear justified (stays left aligned). Can anybody out there verify whether their RTB control displays the text fully justified while editing using this code, or is this for printing purposes only?

Thanks in advance,

Combo Box Text Justification
how can i change the justification of text in a combo box?

i have a combo box that i load with a list of user names and id's. i included 50 odd spaces between the 2 so that the user can't see the id number. i use it for the table lookup field. when the box is loaded the items are left justified but when you select one the text that's put into the combobox's text field is right justified.

any ideas on how to make the combobox.text left justified? any why they would do this? :-)


Justification Problem When Printing
My text is justified to the right in all my text boxes but when it prints it's realigned to the left???
Would appreciate any tips...thanks!!!


Dimitrijevic B.

Justification Of A Picture On A Checkbox
Hi all,
    Does anyone know of a way to have a picture right justified on a checkbox (style = 1-graphical). I also have a caption for the button. One idea I had was to have the caption and the picture (a hand pointing to the right) as the picture, but that's a bit of a pain to do (I have a few of these buttons with different captions but the same picture ).

Regards... John

Edited by - jsyer on 2/1/2004 8:04:29 PM

Justification Of A MLine - Multiline

I'm french and i make an application with VBA for Autocad.
I need the word to attribute the justification of a multiline to a variable.


Dim elem As AcadObject
Dim retCoord As Variant
Dim intN As Long
Dim strMsg As String
Dim strObjectName As String
Dim justification As String

For Each elem In ThisDrawing.ModelSpace

strObjectName = elem.ObjectName
strMsg = ""

Select Case strObjectName

Case Is = "AcDbMline"
retCoord = elem.Coordinates
justification = elem.????????
For intN = LBound(retCoord) To UBound(retCoord) Step 3
strMsg = strMsg & "x: " & retCoord(intN) & " y: " & retCoord(intN + 1) & justif & vbCrLf
MsgBox strMsg, vbInformation, "Multiline Coordinates " & elem.Handle

End Select


Thanck you very much


Can An Excel Column Be Passed Into A VB Array In Excel
Is it possible to pass a say 5 values from the same (different) columns in excel into an array using VB?

This is the basis of the code

Private Sub Button1_Click()
Dim numbers() As Integer
Dim txt As String
Dim i As Integer

' Load the data.
LoadNumbers numbers(), Array((Worksheets(1).Range.Cells("d2:d8")))
' Display the values loaded.
txt = ""
For i = LBound(numbers) To UBound(numbers)
txt = txt & Format$(numbers(i)) & Chr$(10) & _
Next i
MsgBox txt
End Sub

' Copy numbers from a variant array to an integer
' array. This will give better peformance.
Private Sub LoadNumbers(arr() As Integer, ByVal var_arr As _
Dim min_num As Integer
Dim max_num As Integer
Dim i As Integer

' Make room for the numbers.
max_num = UBound(var_arr)
min_num = LBound(var_arr)
ReDim arr(min_num To max_num)

' Copy the numbers.
For i = min_num To max_num
arr(i) = var_arr(i)
Next i
End Sub

Rich Text Full Justification
Hello Folks,

Is there any way to make text(non english) in a rich text box fully justified ? I got a project for print previewing which handles full justification but it only justifies the text and prints it to a picture box and then send the dosument to the printer.

I do not want to send the justified text to the printer, I need to select the rich text in the rich text box and then by pressing a button the text need to be fully justified.I need this code urgently.If any body can help me out it would be a great help for me.



Data Justification In Rich Text Box
Hi sir..

We r developing CBTS using VB6.0 where we r using RichText box control for displaying data.We have taken care left alignment
only while data entry done.Now my problem is Displayed data
should be Justified in Rich Text Box control..

so please help me in finding the solution

Thank u


Crystal Reports 4.6 - Field Justification
Hi friends,

How can I justify a text or memo field on a Crystal report ?

Thanks for any help,

Data Justification In Rich Text Box Control

We R Developing CBT 's with Using VB6.0 and MS Access..
while inserting data we r taking care of left alignment only.
At the same time while retriving data, the format should be justified in Rich Text Box Control..How to do this?

please advise this

Left Justification For Numeric Field In Access
I have a numeric field in a table and when i try to 'export' this tbl in "Fixed format" the numeric field is right-to-left justfied. I want to be left-to-right. how do i do this.


lets say there are 3 col in this table or you can create one at your place and try.

1. Fname - Length (15)
2. Age (long integer)
3. Lname -Length (20)
if the data in these 3 fields is as follows:

John | 25|doe
Mary | 17|beth

when i export using file->export in fixed format what i get is
John |25 |doe
Mary |17 |beth

if you notice it is right-to-left justified, i want left-to-right justified. thanks.

Excel Issues - "Excel Cannot Determine Which Row In Your List Contains Column Labels"
Hello again,

I am working on a program that imports various Excel spreadsheets into one one workbook. The data is then sorted and calculations are made which are then summarized into one sheet. I have created a form that will enable the user to select which kind of charts they would like to generate and for what, all based on the summary of the data.

The issue I am faced with is an Excel prompt that comes up whenever I use the subtotal function for my calculations. The prompt states "Microsoft Excel cannot determine which row in your list or selection contains column labels, which are required for this command." I use the subtotal function on a number of different worksheets, so this prompt is coming up 5 times.

The imported data does have bold column headings as well. Is there a way to force these prompts to say OK which tells Excel to use the first line of data as my column headings without these prompts coming up? Or is there any other quick fix for this? Any advice is very much appreciated.

Add Column In Excel With DAO
Is it possible to add a column to a excel sheet using DAO. I already have the code to connect to the excel shhet and to modify the data. I just need the code to add a column. When i use field.append i get Invalid Operation.

- Thank you.

Excel VBA && Last Column
Is there a way to take the code below, and have it find the last empty column?


Dim LastRow As Long
Dim wSheet

On Error Resume Next
Set wSheet = Worksheets(1)
If wSheet Is Nothing Then
With wSheet
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
Range("A" & LastRow).Select
'Range(LastRow & "C").Select
ActiveCell.Formula = "TEST TEST TEST"
End With
End If
On Error GoTo 0

If any further information is needed, please let me know.

Any help on this issue will be greatly appreciated.

VBA Excel Column Name
Assume that in row = 2 and column = 3 in excel we store value "111"
Then using VBA i want to get the output as
Value "111" is stored in C2......
i am able to get everything as desired..
but my problem is....
is there any way that directly convert column number into the column name...
Column(3) = "C"....
Looking forward for the help...........

currently i have coded my own convert function...
in which i have written..something like :
if column = 1 then return "A"..
if column = 2 then return "B"..


Excel 2K Need To Remove Every Other Column
Hi All,

I'm writting a macro to convert the formatting of some spreadsheets, some sheets have only 4 columns, others have over 200. I've written a crude way to delete every other column, but its not flexible.

' Step 6 - Deleting Qualifier Columns

Selection.Delete Shift:=xlToLeft
Can any one suggest a better way to delete every other Column starting at column 'C'?

Since some spreadsheets will have only a handful of columns, and others will have close to the limit (220? I think is excel's limit). I've been trying to figure out how to write an expression that will avoid the columns I want and only select 'ever other' column.

Any help would be appricated.

Move To The Next Column In Excel Using Vb6

I am writing a program that populates data from a sql database in Excel. The problem is that the data for the new month is overwriting the old data in the first column, could you help me with code to move to the next colum and dump the data there instead.

Highly appreciative of your prompt reply.

This is a slippet of my code so far


curMonth = Month(Date)

cn.Open cString
For m = 1 To curMonth

MerchantMonthly = "select a.sp_name as Branch, sum(b.qty) as Invoice_qty, sum(b.value_incl_vat) as Invoice_value_incl_Vat" & _
" from service_providers a left outer join cb b on a.sp_name = b.sp_name and month" & _
" (inv_date) = " & m & " and year(inv_date) = 2004 and item is not null group by a.sp_name" & _
" order by a.sp_name"

If rs.State = -1 Then rs.Close

rs.Open MerchantMonthly, cn, adOpenKeyset, adLockOptimistic, adCmdText

'dump data into excel by calling function
Call PopulatePage(m + 2)

'set formatting defaults for displaying the data
formatcell = formatcells

rs.Close ' Close the recordset for next loop

Next m


Set rs = Nothing
Set cn = Nothing

Public Function PopulatePage(Optional y As Integer = 2)
x = 1

While rs.EOF = False

x = x + 1
For m = 0 To rs.Fields.Count - 1
msSheet.Cells(y, m + 1).Value = ProperCase(rs.Fields(m).Name)
msSheet.Cells(y, m + 1).Font.Color = vbYellow
msSheet.Cells(y, m + 1).Font.Size = 12
msSheet.Cells(y, m + 1).Font.Bold = True
msSheet.Cells(y, m + 1).Interior.Color = vbBlue
msSheet.Cells(x, m + 1).Value = rs.Fields(m).Value
Next m
End Function

Excel Column Headings

I'm trying to export data from an ADODB recordset to Excel in Access VBA. I'm able to do this, but I want to put column headings in the first row of cells. I've seen code examples that show how to add values to the A1, B1, C1, ect fields, but they are overwritten when I use the .CopyFromRecorset method. Any suggestions? Ideally I'd like to pull the column headings from the record set field names rather than hard coded values.


PS - Here's the code I'm using...

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook, xldata, chrData
Dim xlsheet As Excel.Worksheet, xlName, xlRow, xlClm
Dim xlRS As ADODB.Recordset

'Turn on the hourglass
MsgBox Screen.MousePointer

Screen.MousePointer = 11

'Declare the file name that you want to save
xlName = "c: est.xls"

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlsheet = xlBook.Worksheets.Add

xlsheet.Cells.CopyFromRecordset sfrmServersList.Form.Recordset

xlsheet.SaveAs xlName

'Quit the Excel application

'open the workbook
xlApp.Workbooks.Open xlName

Set xlApp = Nothing
Set xlBook = Nothing
Set xlsheet = Nothing

'Turn off the hourglass
Screen.MousePointer = 1

End If

Column And Row Height In Excel
Hi, I was wondering if anybody has any code to size a perticular row or column in pixels in excel. I know the one of the two is measured in characters, but I was wondering how to do it?

Excel Column Reference
Morning all,

I have a column reference passed to my program via an ini file. At the moment its 'BB' but is liable to change on a frequent basis.

I need to reference this column as a number rather than its alpha value. Does anyone know of a formula for converting 'BB' to 54 or a column property that I can use?

Scary (looking forward to the FA cup draw)

Filter Column In Excel
I'm really new in VB so can anyone please help me with this?? I'm using VB6 in making an application:

I have create a listbox(checkbox) that can read specify column in An Excel spreadsheet. I'm using DAO recordset to read this column. Then, in the listbox, user can only select one data entry from the column data. However, each data selected may not be unique, meaning that in the spreadsheet, one unique data may carry a number of rows. Now the problem is that I want to open back the Excel sheet... But this time filter the column data so that only table with selected data exists. Can anyone help me with this?

I've sampled out my code as well for assistance. I'm new at this site so please tell me if I should post this elsewhere. I really appreciate any help that comes.

Private Sub Form_Load()

Dim functioncall As String
Dim add As String
On Error Resume Next

Me.Move (Screen.Width - Me.ScaleWidth) 2, _
(Screen.Height - Me.ScaleHeight) 2
Me.txtAddress = frmMain.Text1
add = Me.txtAddress
functioncall = Initialize(add)

End Sub

Function Initialize(addstring As String) As String

On Error GoTo Errhnd
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

Set db = OpenDatabase(addstring, False, False, "Excel 8.0;HDR=NO")
Set rs = db.OpenRecordset("Select distinct * from [C1:C65536];")

With rs
NoOfRecords = .RecordCount
While Not .EOF
lstCallingDNA.AddItem .Fields(0).Value
End With


Set rs = Nothing
Set db = Nothing
Exit Function

Errhnd: MsgBox Err.Number & " " & Err.Description & " Error Generated By " & Err.Source, vbCritical, "The Excel file specified is in different table format"
Exit Function
End Function

Private Sub lstCallingDNA_Click()

Static InSub As Boolean
Dim X As Byte

If InSub Or lstCallingDNA.ListIndex < 0 Then Exit Sub
InSub = True
For X = 0 To lstCallingDNA.ListCount - 1
lstCallingDNA.Selected(X) = X = lstCallingDNA.ListIndex
InSub = False
End Sub

Excel Protection By Column
I went through a search on this foum but have not been able to find an answer to this.

Becasue my app relies on data being in certain columns (not very robust, i know) I would like to be able to protect the worksheets so that the users can not insert new columns.

Does anyone know how to do thisor better yet can this be done?

Automatically Add Sum Of A Column In Excel
Here's the story....

I have a function in an Access Db that automatically exports data to Excel. The procedure exports the data, formats the width of the columns and adds the column headings. One of the columns (column C) lists the size of libraries (in Mb) on an AS400. What I want to do during the export procedure is to have the total sum of this column appended to the bottom of column C. If possible, I would prefer there to be a blank row first between the last value column C and the total sum of the libraries.

Can anybody help??

Excel Column Manipulator
Hey everyone, I'm new to this and was hoping someone could give me some help here. I'm trying to compare cells in two columns and then change the cell in a third column if the comparison cells are not equal to each other.

For counter = 1 To 38

If ActiveCell <> ActiveCell.Offset(0, 3) Then

ActiveCell.FormulaR1C1 = "=R[-1]C+1"
ActiveCell.Offset(0, 1).Activate
ActiveCell.FormulaR1C1 = "0"
ActiveCell.Offset(0, 1).Activate
ActiveCell.Delete Shift:=xlUp
ActiveCell.Offset(1, -2).Activate

Else: ActiveCell.Offset(1, 0).Activate

End If

Next counter

The problem seems to be with the "ActiveCell <> ActiveCell.Offset(0, 3)"
portion, I don't believe this is ever really checked for the inequality. The
macro just moves forward with the cell change to 0 regardless of whether
the column cells match each other.


Last Used Excel Column (Access VBA)...
Office 2002.......

i assume you can something like this to get the last used column....

Dim lngCol as Long
lngCol = .ActiveSheet.UsedRange.Columns.Count
how would i then use this in a range such as...

Xlapp.Range(A1:How can i use it here).Font.Bold=True
Essentially i want to be able to detect the last used column and then specify it within a range so i can make my code more dynamic. A typical piece of code i am using at the moment looks like this......

lngRw = .ActiveSheet.UsedRange.Rows.Count
.Range("A1:I2").HorizontalAlignment = xlCenter


.Range("A3:B3").HorizontalAlignment = xlCenter

.Range("D3:E3").HorizontalAlignment = xlCenter

.Range("F3:G3").HorizontalAlignment = xlCenter

.Range("H3:I3").HorizontalAlignment = xlCenter

.Range("B5:B" & lngRw & ",F5:F" & lngRw).Font.Bold = True

.Range("D4:I" & lngRw).ColumnWidth = 9

i can happily use the lngRw variable to detect the last used row but how to do it with columns that are letters. So in the third last row above the i don't have to hard code the letter F

any ideas, couldn't quite find anything concrete searching around.


How To Set Excel Column Type In VB6?
Hi all,

I got a question about excel with VB6. I am using Excel dll to write to a excel file from VB6. However when I try to insert 010106 into the column, it will always save as 10106. Does anyone have any idea to set the column properties of excel thru VB6? Thanks.

Get Column Heading In Excel From VB .NET
Is there a way to get the Column Heading like A, B, C ....etc
based on the column index? For Example, index 3 corresponds
to C. Thanks for your help!

Excel Column Totals
I need to write a macro in Excel that will create 2 separate totals at the bottom of a data set in 2 different columns and have the flexibility to handle varying data ranges.

Using the subtotal command doesn't work because it creates 2 totals on top of one another. Using the Sum command doesn't work because my datasets vary in length.

Itís probably an easy VB question but I canít figure it out.

Importing An Excel Column.....
when I start excel, my main user form displays. From here, there are 3 command buttons. A click on either of the 3 buttons, takse me to my listbox page(lstpg). I want to only have 1 listbox userform, but allow data from any of the respective columns(A, B, or C based on the command button click) to display in my listbox. I have tried a few different things, but can't get the data to display on a second userform (the one with the listbox). Anyhelp is greatly appreciated.

Also, on the listbox page, Ihave a command button that takes you back to the to main user form. Can I clear the listbox as this happens to ready it for the next column import.

I'm such a n00b. thanks everyone.

Copyright © 2005-08, All rights reserved