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




Cell Printing In Excel With VBA


Hello!

What macro can I use for printing 2 specific cells of the selected row in an Excel sheet (say the 1st and the 5th)?




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Printing Excel Cell Selection -Troubled Novice Needs Help
Hi everyone!

how can i print a selection on an active worksheet in visual basic code? for example, i want to print all the values within the range A1 upto Z100?

how can i setup my printer for such action in VBA?

many thanks

Excel - Making A Cell Equal To The Value Of The Formula In The Cell
I thought I'd seen something on this already, but I coudln't find it. My appoligies if I'm creating a duplicate thread.

I'm writing a macro in excel to fill in data in a database. Unfortunately, my titles for each entry have to be taken from multiple cells and put into one cell. The code to place the title in the title cell is:

Code:Dim title As String
    title = inputname & "!$B$3, " & inputname & "!$C$3, " & inputname & "!$D$3, " & inputname & "!$E$3, " & inputname & "!$F$3, " & inputname & "!$G$3, " & inputname & "!$H$3, " & inputname & "!$I$3"
    title = "=CONCATENATE(" & title & ")"
    ActiveCell.Value = title

It works fine. The problem I'm having is that every time I save and reopen my database excel pops up with this "The workbook you opened contains automatic links to information in other workbooks..." It's getting very annoying. And it also gives me problems if anybody touches the already filled title cells. I was thinking that there might be a way to make the formula equal to the value of the formula. For example making a cell have the value 4 instead of the forumula "=2+2". Thanks for any help.

How To Link An Excel Cell To An Access Table Cell
I am trying to import an populated excel form into access using just specific cells (where the data is located) only. I am able to open excel and get the data from the cells but how can i assign them back to my access table? This is like importing a populated excel form into access database.


I used these to get my excel info

Set excel_app = CreateObject("Excel.Application")
excel_app.Visible = False
excel_app.Workbooks.Open FileName:=mypathname & myfilename
Set excel_sheet = excel_app
x= excel_sheet.cells(10, 4).Value 'To get the excel cell value'
y= excel_sheet.cells(12,4).Value

Now I want x to be assigned to table "AppendTable" in column "AppColumn1"
and y in column "AppColumn2"

help!!!

How To Read An Excel Spreadsheet Column, That Is, Read From The Top Cell To Bottom Cell (like File R
How to read an excel spreadsheet column, that is, read from the top cell to bottom cell (like file read to eof)? how can I know which cell is the last cell?

thanks

Preventing A Cell Value Printing
Hi all,

I am trying to print a worksheet. The sheet contains cells with text and formulas.

II need to hide the text in some cells when printing. have tried to hide the text in some of the cells by making them the same colour as the background, which is not white, and this works fine visually however when I print the sheet the cells are shown as black text.

The printer is set to blackandwhite =false

Is there a way to make one or two individual cells not print.

I cant Hide the row / column as there are other items on them which need to print and the layout is prity much fixed at this stage.

As always any advice much appreciated

Thanks

Eoin

I Am Exporting An Access Query To Excel, How Can I Define The Excel Cell Size/format
I am using TransferSpreadsheet to Export a Query to Excel with a button from Access.

How can I define in Excel when I export it, the size of the cells, the type of letter (Arial, Bold), The Background color.

Private Sub Impacto_Click()
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
TableName:="Impact", _
Filename:="C:Impact.xls", _
hasfieldnames:=True
End Sub

Find Location Of A Cell In Excel-import From Excel Into Access
Hi
I want to import some columns from an excel spreadsheet into an access table using vb6.

I am going abt this by finding the location of the cell but am having problems this way


Code:
Private Sub ImportExcelAttend()
Dim imA As New ADODB.Recordset 'Recordest for temp table
Dim ex As New Excel.Application
Dim I, J, a As Integer
Dim TheFile As String

imA.Open "select * from tbl_tempAttend", MyConn, , adLockOptimistic

ex.Workbooks.Open txtLocation.Text
ex.Cells(I, J) = "%att"


however i cannot get it to work. any help this way would be appreciated.

OR if you know another way to import these few colums with unknown location of cells into access i would be very grateful. Location will always change every time.

Regards
JohnnyH

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:

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.

Thankyou,
Chock.

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
                
            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

Thankyou,
Chock.

Printing A Mshflexgrid Cell Adds A Cr
I have a small 2 column (likely to add more columns later) table done with a mshflexgrid. I'm trying to print a "report" of this table, basically just print the whole thing putting tabs between the columns. However, when it prints a cell it adds a carriage return after the item. What i want for output is this:
col1 col2
col1 col2

What i am getting is this:
col1
col2
col1
col2

Here is my relevant code.

' user wants to print grid
Private Sub cmdPrint_Click()
Dim i As Integer, j As Integer

For i = 0 To Me.MSHFlexGrid1.Rows - 1
For j = 0 To Me.MSHFlexGrid1.Cols - 1
Me.MSHFlexGrid1.Col = j
Me.MSHFlexGrid1.Row = i
Printer.Print Trim(Me.MSHFlexGrid1.Text) & vbTab
Next j
Printer.Print vbCr
Next i
Printer.EndDoc
End Sub

Printing A Polygon Into A VsFlexGrid Cell
Hello guys,

I need to insert a polygon into an cell.

Example: when i click on that cell, if col = 2 , then cell's content became a star. If there is a star, became empty.

This star i wanna created by code. Its possible? Can you help me?


Thanks since now.

Repeatedly Altering A Cell Value && Printing The Result.
I have a spreadsheet which when I enter a value into a particular cell it uses SQL to get other data from a database, and puts this information into a useful format for printing. I often have to print over 100 different values at a time which is very time consuming. I have never used VB but have been told that I could enter a list of values in another part of my spreadsheet and then just click on a button which will go down these values, insert each one into a particular cell and print the result (after my queries have automatically done their bit). I do not know where to start with this so would be grateful if someone could point me in the right direction.

Thanks!
Duncan

PS Could someone recommend a downloadable basic tutorial for using VB in excel?

Cell Format, Determining Type Of Cell Content (string/real/integer) And Cell Size
I am not sure whether I am at the right forum, but I am exporting Excel values in a Word document.

Unfortunately -my copy paste action doesn't take in account the cell_properties, so in some cases a value of 9,9999999999999999999999999999998E-4 is entered instead of the 9,9 E-4 or even 0,001 that is shown in the excel sheet.

I'm having trouble with finding the set format command (e.g. Tutbo Pascal: write(value:8:3) -or someting) and I also have strings in these cell -I am not sure if it gives any problems if one sets every zell to 2 decimals, if so -ho can I determin the type of cell content ...
And how to set the cell size to size of content

Could anyone help me, I 'm much obliged

Problem Printing EXCEL After Printing Using A Macro In WORD
I have a strange problem...

I have created a WORD template which contains some buttons to print the document on a certain printer, with or without a logo. This is some standard VBA and works OK.


Code:
Application.ActivePrinter = "HP LaserJet 4250 PS"
ActiveDocument.PrintOut
Now here is the problem:
A document is printed using the macro in WORD.
Then if I want to print something in EXCEL to the "HP LaserJet 4250 PS", nothing is printed!!
However, if I print the EXCEL-sheet to any other printer, it is printed and then the excel sheet can be printed again on the "HP LaserJet 4250 PS"...

(I use Office 2003)

I have no clue what is causing this problem and how to solve this...

Can anyone help me with this??

Writing A Cell Address Of A Cell In 1 Worksheet To A Cell In Another In Same Workbook
Hi……..
Its an urgent requirement….
I have an excel sheet. I need to write the macros in VB for the following requirement:
There are three worksheets in a single workbook.. worksheet3 contains some values in every cell. whenever user writes in something in the cell in worksheet1, the column number and row number from worksheet3 must get written automatically in the adjucent cell in worksheet1 only.

For example:
A worksheet 3 contains PLZ A,B in row 5 and column 7. These rows and columns are user defined. If i write PLZ 31, 20 in F26 in worksheet 1 then automatically G26 must contain value 75, G27 must contain a value 31 and G28 must contain a value 20.

I can't say when this value should get populated. Either on the lost focus of F26 or i need to run a macro manually for F26.
 what would be the code in both the cases? Is the former case possible? If i select group of cells like F26 present in worksheet1 and altogether run a macro then would it give the result?

Thanks in advance for your reply.
Regards...
Priyadarshini

Excel Cell
Hi ,
if i have this in one of the cells in excel

my name is king

is there a way to automatically put underscores so that we get an output like this

my_name_is_king

pls confirm > do we need to write any macro or any other automated way ????
thanks

Excel Cell Value Into VB
I know very little about VB and have scraped together a very effective solution (from the internet) to meet my Excel needs. But,...
In the code below, rather than hard coding the string "C:My Docs etc", I want to be able to use a cell value which has this text

Here is the beginning of the Sub

Dim p As String, x As Variant

p = "C:My DocumentsBradlah blah blah"
x = GetFileList(p)

Would appreciate the help
Thanks

Last Cell In Excel
hi this is the code i use to find the last cell row in an xcel sheet.. when i use this code, the err msg displayed is
Object doesn't support this property or method: 'specialcells'
i tried using objExcelApp.range.activecell.specialcells(xlCellTypeLastCell).row. it too didn't work.. pls help me.. but this worked fine as a excel macro

<html>

<head>
<%
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Workbooks.Open "d: estexcel est11.xls",,True
response.write objExcelApp.activecell.specialcells(xlCellTypeLastCell).row

%>
</head>

<body>

</body>

</html>

How To Get The Last Cell From Excel In Vb?
How can i determine which is the last cell in an excel column through VB code?

Getting Cell That Has Mix Font In Excel
Hi all

When I try to find some text in excel with some specific font format, it return me the whole cell range, even if this cell has two fonts. And more badly it can not find those cells that has that specific font but the starting text of the cell has different font.

The only solution that I have, to scan each cell character by character and find the required specific format. But this solution is very slow. Can you please suggest some better solution?

Best Regards
Tola

How Do You Refer To A Cell In Excel In VB.
Hey all!

I have VERY slim knowledge of VB, but would like to know how to solve this problem.

If MoneyAdding!B1 = "Offerings" Then
'Macro name follows
Offerings_macro

ElseIf MoneyAdding!B1 = "Gift Aided giving" Then
GiftAidedgiving_macro

ElseIf MoneyAdding!B1 = "Gifts" Then
Gifts_macro

Else
PaperRecycling_macro
End If

I'm guessing the MoneyAdding!B1 = "NAME" part is wrong as its what I would write in excel.

Basically I dunno if I wrote my If... Then.. Else correctly. But I want to click a command button on a userform and if the word Offerings is in the Cell B1 on the worksheet MoneyAdding then it will run my macro. Or if it is any of the other names it runs that macro.

BE very greatful for help. How would this be written?
Andy

Excel Active Cell
G'day,

This may seem a little simple, but I have no idea!!

I want to store the position of the current active cell so I can come back to it after a loop has finished. How should I go about this?

Cell Name Referencing In Excel
Hey, I have a quick question for you..

Referencing a cell is easy; B1 is .Cells(1, 2), right? Well, what if B1 was given the (variable)name "CustName"? Hence, the word "CustName" is where you normally would get "B1" in on the FormulaBar in Excel. How do I reference the value in that cell? .Cells(Type)? Don't think so.. Please advice!

Thanks
Toby

Excel Help - First Empty Cell
Hi,

I would like to select the first empty cell in a column and then add text to it from a textbox on the userform. What I use is

Code:
Sheet2.Range("B1").End(xlDown).Offset(1, 0).Value = TextBox1.Text
and that works fine,

but then when I try

Code:
Sheet2.Range("J2").End(xlDown).Offset(1, 0).Value = Sheet1.Cells(1, 4).Value
I get the error

'Run-time error '1004':
Application-defined or object-defined error'

what am I doing wrong?

Thanks,

Jim

Excel Cell Color
I am using a combo box to add information to an excel spreadsheet, no problem so far. But the 5 choices each unique info needs to be entered into the spreadsheet with a unique cell color....lightgreen cell for choice one, lightblue cell for choice two...etc.
So depending on which selection is made from the combo box I need not only the text selection but also to change the individual background color.
Is this possible and how would it be done?
Any help would be appreciated.
thanks

Two Values In Excel Cell
Can you store two values in an excel cell? I don't think comments will work because I don't want the user to know the value that is not being displayed.

Validating A Cell Value In Excel From VB
Hi
I m facing a problem in validating the cells of an excel sheet.
I want to read an excel sheet cell by cell and check if all values are valid like e.g.if a cell contains value 8$ ,I want to read it as 8,(only integers) and ignore the special characters and store the value in a variable and give message to the user saying the particular cell is invalid
Can anybody help me please...

Thanks in advance
jaishree

Excel Zero Vs Blank Cell
When I use Cells(A, B) to iterate though a range, blank cells return a value of Zero.

Does anyone have a suggestion how to distinguish between blank cells and those which have the value Zero in them?

Excel, Select Cell Where
Ok im doing visual basic in excel at the moment and have a drop down validation list thats linked to another worksheet, i want to create a macro that selects the cell that contains the item that the user selects from the list,
eg: A1:E1 is the list data in sheet1
the validation list shows the contents of A1:E1 in sheet2
so if the user selects the 2nd item on the list in sheet2 (which would be B1) the focus will go to B1 in sheet1
doesnt matter if this goes through a loop and goes through the list and selects the item that matches the one selected:
eg was thinking along the lines of:
select cell in list range where it is = to the selected item

dont know what ways are possible though, any help will be much appreciated
cheers!

Put Cell Value Into Excel Graph
Hi All,

I have a graph in worksheet (Chart 1 month), and I have stats in worksheet (Stat).

What I need to do is show a value from sheet (stat) Cell B15 (in text box or something) on the Graph in worksheet (Chart 1 month).

Thanks

Gary

Excel Cell Value Is Not Empty
Hello,

Sometimes I do not understand simple things but maybe somebody else. I'am testing a cell value but I do not understand why the if statement is true:

if (IsNumeric(trim(wshSubnet.Cells(i, wshSubnet.Range("DBB_CB_NAME"& i).Column))) and _
(cint(trim(wshSubnet.Cells(i, wshSubnet.Range("DBB_CB_NAME"& i).Column))) > iCountInstal )) Then

Now: the value of the Cell is 2; iCountInstal = 2
DBB_CB_NAME"& i is a cell name

What I do not understand how can this be True? Because in my opinion the the value of the cell is 2 and is not bigger than iCountInstal which is 2 as well. The same problem do I have when the value of iCountInstal = 1
I think I make somewhere a stupid mistake but I don't see where

Must I do a type conversion first?

Nico

Excel VBA Cell.value = Error 400
I have a sub routine (for a timer) that enters a Range ("A1").Value = xx and it runs through the loop, counting up or down (depending on another variable). Everything works great, but as soon as I put focus on another cell (i.e., start typing in B1, or F2 in B2) I receive the Error 400 message.

I understand why I am receiving the error, but I need to know how I can get Excel NOT to display the error dialog box, then wait until I get out of whatever cell I happen to be in and continue the timer where it left off.

Any help would be greatly appreciated, I've been running in circles for hours with this problem!!

Thanks

Excel Cell AutoFit
Hi I'm having some trouble with autofit and colwidth of Excel cells. I have searched the forum and tried the code
with similar problems from replies but still can't get this to go

Things like number format work fine

No matter what I try I can have no effect on the width of cells and therefor the display
is a little jumbled.

I would be grateful for any help
Thanks


sub to create the excel report sheet


Code:
Sub CreateExcelSheet(ExcelName As String)
On Error GoTo handle
strSheetName = ExcelName
'Dim security As SECURITY_ATTRIBUTES
iFileNo = FreeFile()
'name excel file
'create an excel spreadsheet name based report needed
strFileName = "C:" & strSheetName & ".xls"
Kill strFileName
Open strFileName For Output As #iFileNo
Close #iFileNo
Exit Sub

handle:
If Err.Description = "File not found" Then
Resume Next
End If
End Sub


sub to populate data to the report sheet


Code:
Public Sub CreateExcelReport(strReportData As String, Optional strSQLCode As String)
'On Error Resume Next
Select Case UCase(strReportData)

Case "GGWAPPLICANTS"

' Sql to locate all applicants for GGW scheme who have not cancelled.
' This takes in all who may not yet have made payment (these could be in limbo indefinately)

strSQL = "SELECT applier.title + ' ' + applier.forename + ' ' + applier.surname AS [FullName]," & _
" pd_Addresses.PRIMARY_ADDRESSABLE_OBJ_NAME," & _
" pd_Addresses.SECONDARY_ADDRESSABLE_OBJ_NAME," & _
" pd_Addresses.STREET_TEXT," & _
" pd_Addresses.LEVEL4_NAME," & _
" pd_Addresses.POSTCODE," & _
" CustomerBins.BinSize," & _
" CustomerBins.Quantity" & _
" applier.payment_date," & _
" LoughboroughSplit.location" & _
" FROM applier INNER JOIN pd_Addresses ON" & _
" applier.uprn = pd_Addresses.UPRN " & _
" INNER JOIN CustomerBins ON" & _
" pd_Addresses.UPRN = CustomerBins.UPRN " & _
" LEFT JOIN LoughboroughSplit ON" & _
" applier.uprn = LoughboroughSplit.UPRN" & _
" WHERE applier.cancel_date ='01/01/1900'"

strHeadings(0) = "NAME"
strHeadings(1) = "HOUSE NONAME"
strHeadings(2) = "PROPERTY ID"
strHeadings(3) = "STREET"
strHeadings(4) = "TOWN"
strHeadings(5) = "POSTCODE"
strHeadings(6) = "BIN SIZE"
strHeadings(7) = "BIN QUANTITY"
strHeadings(8) = "PAYMENT DATE"
strHeadings(9) = "LOUGHBOROUGH LOCATION"



Case "GGWPAIDAPPLICANTS"

' Sql to locate all applicants for GGW scheme who have not cancelled.
' This shows all who have made payment and not cancelled

strSQL = "SELECT applier.title + ' ' + applier.forename + ' ' + applier.surname AS [FullName]," & _
" pd_Addresses.PRIMARY_ADDRESSABLE_OBJ_NAME," & _
" pd_Addresses.SECONDARY_ADDRESSABLE_OBJ_NAME," & _
" pd_Addresses.STREET_TEXT," & _
" pd_Addresses.LEVEL4_NAME," & _
" pd_Addresses.POSTCODE," & _
" applier.email," & _
" applier.tel_no," & _
" applier.enter_date," & _
" applier.payment_date," & _
" applier.receipt_no," & _
" applier.start_date," & _
" CustomerBins.BinSize," & _
" CustomerBins.Quantity" & _
" FROM applier INNER JOIN pd_Addresses ON" & _
" applier.uprn = pd_Addresses.UPRN " & _
" INNER JOIN CustomerBins ON" & _
" pd_Addresses.UPRN = CustomerBins.UPRN " & _
" LEFT JOIN LoughboroughSplit ON" & _
" applier.uprn = LoughboroughSplit.UPRN" & _
" WHERE applier.cancel_date = '01/01/1900' AND" & _
" applier.payment_date <> '01/01/1900' "

strHeadings(0) = "APPLICANT NAME"
strHeadings(1) = "HOUSE NONAME"
strHeadings(2) = "PROPERTY ID"
strHeadings(3) = "STREET"
strHeadings(4) = "TOWN"
strHeadings(5) = "POSTCODE"
strHeadings(6) = "EMAIL CONTACT"
strHeadings(7) = "TELEPHONE CONTACT"
strHeadings(8) = "SYSTEM ENTRY DATE"
strHeadings(9) = "PAYMENT DATE"
strHeadings(10) = "RECEIPT NO"
strHeadings(11) = "START DATE"
strHeadings(12) = "BIN SIZE"
strHeadings(13) = "BIN QUANTITY"

end select

iColWidth = 0
Call OpenBinSchemeDatabase
Set rsReportRecords = New ADODB.Recordset
rsReportRecords.CursorLocation = adUseClient

rsReportRecords.Open strSQL, conn.BinScheme, adOpenKeyset, adLockPessimistic

If rsReportRecords.EOF = True And rsReportRecords.Bof = True Then
MsgBox "There are no records for this Report"
Exit Sub
End If
'set month code
'Sheets = 1
'On Error GoTo handle
With rsReportRecords
'Excel sheet will have one of the above cell heading
strAlphabet = "ABCDEFGHIJKLMNOPRSTUVWXYZ"

Set objExcelApp = New Excel.Application
'excel file to be worked with
Set objExcelWrkBk = objExcelApp.Workbooks.Open(strFileName)
'excel sheets
Set objExcelWrkSht = objExcelWrkBk.Worksheets(strSheetName)


'apply data to one column at a time
For iColCount = 0 To rsReportRecords.Fields.Count - 1

.MoveFirst

'current letter taken from alphabet and identified by position
'based on the column
strLetter = Mid(strAlphabet, iColCount + 1, 1)
iRowCount = 1
strExcelCell = strLetter & iRowCount

objExcelWrkSht.Range(strExcelCell).Value = strHeadings(iColCount)


Next iColCount

'apply data to one column at a time
For iColCount = 0 To rsReportRecords.Fields.Count - 1

.MoveFirst
strLetter = Mid(strAlphabet, iColCount + 1, 1)
'apply data to the columns row
For iRowCount = 2 To .RecordCount + 1

'the use_plus_row_counter = true if a new phone type occurs
strExcelCell = strLetter & iRowCount


objExcelWrkSht.Range(strExcelCell).Value = Trim(NullString(.Fields(iColCount).Value))
If frmStartup.picText.TextWidth(Trim(NullString(.Fields(iColCount).Value))) > iColWidth Then
iColWidth = frmStartup.picText.TextWidth(Trim(NullString(.Fields(iColCount).Value)))
objExcelWrkSht.Range(strExcelCell).EntireColumn.ColumnWidth = iColWidth
'objExcelWrkSht.Range(strExcelCell).NumberFormat = "-0.00"
End If

.MoveNext
Next iRowCount
iColWidth = 0


Next iColCount


strFirstLetter = "A:"
strLetterRange = strFirstLetter & strLetter

objExcelApp.DisplayAlerts = False

objExcelWrkSht.Columns(strLetterRange).AutoFit

objExcelWrkBk.SaveAs (strFileName)
objExcelWrkBk.Close
objExcelApp.DisplayAlerts = True

objExcelApp.DisplayAlerts = False
objExcelApp.Quit
objExcelApp.DisplayAlerts = True

Set objExcelWrkSht = Nothing
Set objExcelWrkBk = Nothing
Set objExcelApp = Nothing
.Close
End With
Set rsReportRecords = Nothing
ShellExecute frmExemptPropertyReport.hwnd, "open", strFileName, vbNullString, vbNullString, 1

End Sub


As I use this code for a report for several different functions I don't know how many columns
will be used so I count the cols then I access each cell based on the Column and the Row
so strExcelCell will be anything from A1 to K3253 dependant on the records I have to populate
the sheet.

I have tried


Code:

objExcelWrkSht.Range(strExcelCell).EntireColumn.AutoFit
tried to evaluate the text width of the the text to be input to the cell via a picture box control
using vbtwips - vbmillmtres as a scale which makes no diference.


Code:
If frmStartup.picText.TextWidth(Trim(NullString(.Fields(iColCount).Value))) > iColWidth Then
iColWidth = frmStartup.picText.TextWidth(Trim(NullString(.Fields(iColCount).Value)))
objExcelWrkSht.Range(strExcelCell).EntireColumn.ColumnWidth = iColWidth
end if
Also tried at the end an evaluation of the columns I have used and perform


Code:

strFirstLetter = "A:"
'eg strLetterRange could be "A:H"
strLetterRange = strFirstLetter & strLetter
objExcelWrkSht.Columns(strLetterRange).AutoFit

Detecting Cell Changes From Excel In VB6
Hi,

I have no idea how to convert a vba event procedure to vb6 . Let´s suppose that Sheet "Test" has this code:

Code:
If Not Application.Intersect(Target, Me.Range("A1:C10")) Is Nothing Then
' At least one cell of Target is within the range A1:C10. Carry out some action.
Else
' No cell of Target in in the range A1:C10. Get Out.
Exit Sub
Could you help me?


Edit by Moderator:
Please post Excel questions, in the Excel forum.

Thank you.

Excel Read Only Cell
Hello,

I made a complicated excel spread sheet to do many calculations and print in a nice well formatted manner. I do not want to user to be able to edit the formulas is there any way I can lock specific cells, make them read only sort of like you do with a text box on a form?

Thank you,
John

Setfocus On Excel Cell
is it possible to setfocus of an excel cell if i have the row and collom poition

PINO

Excel Cell Validation
Hello,
I would like to validate some cells in a spreadsheet so that users can only enter data in the format dd/mm/yyyy hh:mm.

Also, is this validation possible in a csv file?

thanks a lot for the help

cheers

Getting Information From A Cell In Excel
Basically I'm 100% new to VB6 and MSExcel integration, and I'd like to know how to gain the infomation from a specific cell in an Excel spreadsheet and load it into a variable (as a string) so that that variable can be put into a TextBox in VB, without opening the Excel spreadsheet. Any help would be greatly appreciated.

Another thing that I would like to know is how to edit information in a cell and/or input information into a completely new cell in a row. Thanks a lot.

Vba: Formatting Cell In Excel
continuation of my post:

http://vbforums.com/showthread.php?t=486877

how can i format (alignment, background color, border, decimal places...) cell during runtime.

Excel Cell Change
Is there a way to trap the chage of a cell value while you are still in that cell. Right now I can only trap a cell change when the user leaves the cell.

My goal:

I am using an Excel WorkSheet with 2 sheets. If cell B1 on sheet 1 is a 0 then I must show sheet2 if cell B1 on sheet 1 is a 1 then I stay on the current sheet.

Cell Width In Excel
how can i format the column width in excel from VB?


VB Code:
Dim ApExcel As Excel.ApplicationSet ApExcel = CreateObject("Excel.Application")        With ApExcel        .Workbooks.Open App.Path & " abela_precos.xls"        .Visible = True        .Sheets("Plan1").Select        .Columns.ColumnWidth = 50    End With


the line

VB Code:
.Columns.ColumnWidth = 50


put the same width for all columns, anybody knows how can i put diferent widths for diferent columns???


Thanks a lot,
Guilherme Costa










Added [RESOLVED] to thread title and green "resolved" checkmark - Hack

Help!!! To Use Single Cell Of Excel Through Vb 6.0
Dear friend

I am creating a project in excel in which i want to access and use a particular single cell of access and put a text in to it. But I don ot know how to do that


Plz!!! Help me


Thanks in advance


Shivpreet2k1

Accessing Excel Cell
Hi

whenever i need to access an excel cell i seem to need to set the pointer to the worksheet containing the cell in the first place

e.g. i have got


Code:
Set ptrEquipmentTypes = GetObject("D:equipmentequipment types.xls")


defined in the form and i have got a combo box defined elsewhere

but to access a cell of the "equipment types.xls" in say a combo box i need to set the point again in the procedure for say combo_click


can i not set the pointer globally or how can make the pointer globally visible?

Insert Pic Into Cell In Excel
Hi again

How can i insert a pic into a certain cell in my excel sheet? I can insert text using this code


VB Code:
oApp.Sheets(1).Range("D" & Iiloop).Value = "Hello"


But what would be the code for inserting an image?

Thanks

Hugh

How To Get Excel Cell Information In VB
Hi all, just a quicky, ive tried useing some samples but cant seem to get it to work so i thoguth i would ask here.

If i want to search a perticular cell in excel and retreive the information stored there, what would be the command line?

thanks for your time

Excel - Cell Reference
I have


Code:
set MyXL = getobject("c:Fileblah blah")
cell = MyXL.Cells(7,1)


It doesnt like this code....complains about the cell reference saying method or property not recognised...or something like that.

How do i get data from a cell in my excell file???
The above method is obviously wrong somewhere.


Matt

Excel - Cell Name/Reference
Greetings,

I am having some troubles with my latest excel project. When a user types ctrl+m I want a msgbox to popup with the name of the active cell ("A1"). I found some code on the MS Page and it only works if I have named the cell. Is there a way to find out the active cell if I have not named it?

Here is the code from the MS Help Page.

Sub ShowName()
Dim Cell_Name As String
Cell_Name = ActiveCell.Name.Name
MsgBox Cell_Name
End Sub

http://support.microsoft.com/?kbid=213432

Thanks.

Excel Scrollbar Value In Cell
Hi,
In Excel, how would I get the value of the scrollbar to appear in a Cell on the spread sheet. This value in the cell would change when i increase/decrease depending on the movement of the scrollbar?

Thanks

Insert Cell Into Excel In VB
Hi all,


I am currently trying to delete and insert cells into excel via VB when a certain condition is true.

I am able to delete a cell using

XLcells(row, col).delete

but when i use the same principle for inserting a cell

XL.cells(row , col).insert

it doesnt work. I do not wish to enter anything into that particular cells, but to just leave it blank.

Qwerty
007

Copyright 2005-08 www.BigResource.com, All rights reserved