Select Print Range
I have multiple charts on one sheet. When printing i want to select both the charts to be selected..either print on one page or more than one page
Can anyone tell me how to do it through VB ?
-ash
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Print Range Of Pages, Find Length Of Document To Print
Hi, I am trying to accomplish two things.
1) I would like to find out how many pages would print out, IF I were to print. ie. Warn the user: "You have asked to print out 500 sheets at $4.50 a sheet."
2) I would like to choose a range of pages to print out, without that range set as the "default" the next time the print function is called. So, my current printing method below is not what I would like. (Unless there is a way to restore defaults?) It displays the normal print dialog, and remembers the last print range entered by the user.
I am displaying web pages on a form within the webbrowser control. Some are very long, so these features are important to me. How it is done is less important. Any advice is appreciated. Thanks!
Here is my very simple example code:
Code:
Option Explicit
'Load a web page
Private Sub Command1_Click()
WebBrowser1.Navigate ("http://www.vbworld.com/")
End Sub
'Brings up the print dialog box on button click.
Private Sub Command3_Click()
Dim eQuery As OLECMDF
eQuery = WebBrowser1.QueryStatusWB(OLECMDID_PRINT)
If Err.Number = 0 Then
If eQuery And OLECMDF_ENABLED Then
WebBrowser1.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_PROMPTUSER, "", ""
Else
End If
End If
End Sub
Private Sub cmdNavigate_Click()
Dim txtNavigate As String
WebBrowser1.Navigate txtNavigate
End Sub
Print Command In Vb6 To Print A Range In An Excel Worksheetsheet
Thank you for you assistance in populating an excel sheet from a vb6 user form textbox.
I am now struggling to print the excel sheet from vb6 command button.
I can print the worksheet but it is blank as I cannot work out how to set the print range.
Assistance would be appreciated as, after hours of trying, I am about ready to scream!!
VBA/Select Range
If use the following code on an Excel worksheet with only one record, it incorrectly selects the AH1 to AH65536:
Range(("AH1"), Range("AH1").End(xlDown)).Select
Currently, I have to test if there is value in the offset cell but having to write this everytime is upsetting.
I've seen a better way of selecting a range from the top cell to last cell that has a value but I don't know where I've seen it.
Also, anybody know how to test if a column has any values at all in it?
I'd appreciate anybody's help.
Thanks,
Aaron
Select Range
Code:
If ActiveCell = product.Text Then
ActiveCell.Offset(-1, 0).Range("A1").Select
Range("A1:M2").Select
Charts.Add
ActiveChart.Export ("C:graph.jpg")
graphimage.Picture = LoadPicture("C:graph.jpg")
ActiveChart.Delete
Exit Sub
End If
I am basically clicking on an id and then searching it through a database. Then once i found the id i want it to select the current range and highlight it until 'M' and 1 more row down.
I was told that whatever activecell is, the program thinks it is is A1. But i guess when i tested it out, it didnt work. All the time it is just highlighting the first rows.
Could someone tell me how to highlight a activecell and then go across to M?
Select Range
I am currently using the followong code but would like to be able to select the range and run the maco instead of manully inputing. I assume its some type of .select but I have not found the right combo. THANKS
Sub Looping()
Dim rng As Range
For Each rng In Range("A5102: A5547")
rng.Value = procedure2(rng.Value)
Next
End Sub
Select Range
I know there is select top 100 * from tblSomethingOrOther
but is there some sort of select Range 200..300 from tblSomethingOrOther?
I.E is there any way to quickly select the 100 more records that would have come AFTER the first 100?
I tried using paged recordset but that still loads all 57k records before it pages em instead of only 100 records at a time.
Select Range
Using VBScript I am trying to use a range in a select case. I am wondering 1st if it can be done and 2nd if so what syntax?
Private Sub rounding()
Select Case dblaverage
Case 1 To 1.4
dblaverage = 1
Case 1.41 To 1.7
dblaverage = 1.5
Case 1.71 To 2
dblaverage = 2
Case 2.01 To 2.4
dblaverage = 2
Case 2.41 To 2.7
dblaverage = 2.5
Case 2.71 To 3
dblaverage = 3
End Select
End Sub
Range.Select .......VBA Q
Hi,
I am trying to write some code that will find a string in a range, insert a row above the position that it has been found. Copy the value to the new cell above and delete the original.
Then for each cell that the string is found in below the copy, the contents of that cell are deleted.
The code then moves on to the next string and handles it in the same way.
However my code runs into an infinite loop if the string only appears once or twice.
Can anyone help me with this one....PLEASE.
Here is the code;
Sub tidy()
Dim c As Range
Dim z As Integer, x As Integer
Range("F3").Select
x = 3
Do While ActiveCell.Offset(-1, 0).Value <> ActiveCell.Value
If ActiveCell.Offset(-1, 0).Value <> ActiveCell.Value Then
If ActiveCell.Offset(1, 0).Value = ActiveCell.Value Then
ActiveCell.Offset(1, 0).ClearContents
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).Copy
ActiveCell.Offset(0, 0).PasteSpecial (xlPasteValues)
ActiveCell.Offset(1, 0).Clear
' ElseIf ActiveCell.Offset(1, 0).Value <> ActiveCell.Value Then
' ActiveCell.Offset(0, 0).Copy
' ActiveCell.EntireRow.Insert
' ActiveCell.Offset(0, 0).PasteSpecial (xlPasteValues)
' ActiveCell.Offset(1, 0).ClearContents
x = 3
Do While ActiveCell.Offset(x, 0).Value = ActiveCell.Value
If ActiveCell.Offset(x, 0).Value = ActiveCell.Value Then
ActiveCell.Offset(x, 0).Clear
Set c = ActiveCell.Offset(x + 1, 0)
x = x + 1
End If
Loop
End If
End If
c.Select
'z = (x + 3)
'Range("F" & z).Select
Loop
End Sub
ANY help / comments would be great.
Regards,
Rocks
Select Range Problem
Hi,
I need to do the following:
The user does select some columns in one row - lets say C4:F4.
Now the script need to change the color of this selected range. I already did this successfully :-) In the next step, also some different ROWS need to be recolored for the same COLUMNS!
In the example above, the script now should change the color in ROW 9, means C9:F9 and also ROW 15, means C15:F15.
Which row to edit is dependent of a differnt search routine.
The problem I have is to get the correct Start end End Column and just changing the ROW.
How can I do this with a simple code?
Hope it is clear what I like to do
FormulaArray With Range Select Help
Dim D As Integer
D = Sheet5.Range("H1").Value
The value in H1 is subtracting one cell from another
Doesn't work:
Range("B4:C" & D).Select
Selection.FormulaArray = _
"=PICalcDat(R[-1]C[1],R[-3]C[1],R[-2]C[1],R[-3]C[3],""average"",1,1,""MHSPI001"")"
Above returns "Runtime Error '1004' Unable to set the FormulaArray property of the range class"
Works:
Range("B4:C97").Select
Selection.FormulaArray = _
"=PICalcDat(R[-1]C[1],R[-3]C[1],R[-2]C[1],R[-3]C[3],""average"",1,1,""MHSPI001"")"
From preliminary research the above error should come if you are trying to pass a string longer than 255 characters. I don't see why the first bit of code would give this error whereas the second one doesnt; and I can't figure ouot why changing the range in the range select bit would trigger it. Probably something newbish, I just started VBA. The string sent to the FormulaArray queries our database and gives back values based on the parameters fed. Hope someone can help, let me know if you need more info.
How To Use Variable To Select Range??
'Sheets(5).Range("A" & EndIt & ":I" & EndIt).Select
Sheets(5).Rows(EndIt & ":").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
The variable gives me the row that i need then i would like to wither select the whole row or the range that i need in that row and highlight it.
Been trying but its not working for me. thanks.
Select The Range With Data
I used auto filter to cut some of my data into a new worksheet so I could look up and match my data. However, my data has different rows from day to day. When I try to copy the range I cut back to the oringinal sheet, I tried to use Cells.Select, which erase all other data in my original worksheet.
Is there anyway to just select the data range? My data range has cells with no data.
Thank you in advance much for help.
Inputbox Range Select
Hi guys, I'm trying to automate a process I have to do at work which takes me days. I have one sheet called PW for example. Im this sheet I have a colum of cells with three #'s
EX:
ME 1
ME 2
ME 12
MW 35
MW 65
MW 5
I need a code that askes me which cell to start from (IE the one that has ME and the data of 1 and take those 3 #'s and average them then move on to the next 3 and average those and so on and so forth for 142 different names (me, me, s3, pl, etc..)
the data that was averaged goes on another sheet. I'm currently useing this code to select the start box but this is all I have I dont know what to do after this:
Sub GetRange()
Dim rngYourRange As Range
Dim rngCell As Range
On Error Resume Next
Set rngYourRange = Application.InputBox _
(Prompt:="Please select or key in a range.", _
Title:="Office Developer Forum", Type:=8)
If (rngYourRange Is Nothing) = False Then
For Each rngCell In rngYourRange.Cells
If IsEmpty(rngCell.Value) = False Then
With rngYourRange
.Columns.AutoFit
.Font.Bold = True
End With
Exit Sub
End If
Next rngCell
End If
If MsgBox("You did not select a range containing values." _
& vbCrLf & "Do you want to try again?", vbYesNo, _
"Office Developer Forum") = vbYes Then
GetRange
End If
End Sub
Thank I hope you understood all of that.
Select Range Problem
i got a problem when select a range record from A to B to print.My coding as below :
frmviewer.CRViewer91.SearchByFormula = "{Branch.BrCode}>='" + cboFrom + "' and {Branch.BrCode}<='" + cboTo1 + "'"
Set Report = CRO.OpenReport("HelpDeskReportBranch1a.rpt", 1)
when i run this coding it give me run time error : "Argument not optional"
i'm using crystal report viewer and i need to select a range record to print.
so, how should i write it????
Help please
TQ first
VBA In WORD, Is It Possible To Select A Range Of
Hello again,
This should be possible, but I need a swift answer so I'll throw it out to the brains. I have a macro in Word that I am editing in VBA, I need to select all text form searchpoint A to searchpoint B but ndo not know how to set my range up. All assistance is hugely appreciated.
Regards
Sean Caddell
is it possible to select a range of
Select Range In Excel With VB6
Guys
Can someone please remind me how to select a range in Excel? I know how many columns there are but the no. of rows will vary. I know there's a simple way of doing this and I'm sure I've done it before. It's just that I have the memory retention of a amnesic goldfish with altzheimers.
Cheers
How To Select A Range Of Cells
I am converting VBA code in Excel to Visual Basic. I am having a problem when it comes to selecting a range. The VBA code is:
Range("L9:O2000").Select
Selection.Locked = False
So, in VB, what command would I use to select the above range?
Any help would be appreciated, as this is the last command I haven't been able to modify correctly.
How To Select Date Range In VB ???
Hi! I need to add Date Range Selection to our corporate tool. 2 DTPickers are not good enough
I found XE-Date Control on
http://www.xe-soft.com
It is pretty good, but probably someone knows better solution ?
How To Select Range Of Dates In VB App ?
Hi! I need to add date range selection component to my VB form. My chief said 2 datapickers are not good solution. I have found on web only xe date control activex for this purpose.
Can anyone propose something else ?
thanks in advance ;)
Select A Cell Range Using VB?
Hi all,
I have a huge sod off sheets of data, but I only want to extract some bits of it. I am using FIND via VB to locate the start of the data segment I'm after, which works fine. The problem I am having is how to get VB to select from that cell that it has just found to 26 cols accross and 50 rows down, so it can then copy all that data and send it to another sheet.
Normally I would just say Range("A3458:Z3508").select - the problem with that is the data I am after may not always be in the same place on the sheet - hence using the FIND.
I have tried with statements, offset & selection meathods, but all of them move the active cell focus to the end - it does not select all the range, just the 1 cell.
Any Ideas anyone.
Select Range Between Letters
Visual Basic 6 Sp5
SQL Server 2000
--------------------------
i want to make sql select to select items between two words like
select * from items where itemno >=ab and itemno<= dr
itemno is nvarchar(15)
but these not give the correct values
Is there any way to solve these to be able to select between two letters or numbers value
Thanks
How Do I Select A Range Without Having To Specify Exact Location?
I just need to have the form command select the next row down and then select the A,B,C range. Just select it without me having to specify which cells such as Range ("a1:c1").select. I want to skip the ("a1:c1") part and have it select a,b and c whichever row is selected at the time. I don't want the whole column just the first 3 and the if I move data around using a form, I can click on commandbutton and it will go down next row below and automatically select the 1st 3 cells.
I hope it makes sense
using VBA with Excel
thanks
Select Specific Cell Range
Microsoft Excel 2003
Microsoft Visual Basic 6.3
Hello,
I am trying to make my macro select a range of cells whose row numbers are calculated within the macro.
Unfortunately the method I used doesn't seem to work, it tells me there is a 'Run time error 1004' and the debugger goes to the offending line (the second line of code shown below).
I originally recorded the macro, and then edited it to be able to automatically select a different range.
I originally had:
Range("B34:B37").select
And changed it to:
Range("B[(p + 1) - (r - 1)]:B[p + 1]").select
p & r are both defined beforehand and set equal to numeric values which depend on certain other factors.
This is probably a ridiculous way of going about it and I know very little about programming, but if anyone knows the correct way then I could REALLY use some help.
Thanks,
Henry
Select Range Textbox Available Programmatically?
Is there a control that behaves like the Select Range textboxes in chart wizard? See attached pic. I'd like to be able to have one of those nifty little deals on a userform. Thanks in advance.
Select Range Of Cells Same Contents
How can I select a range of adjacent cells having the same content?
The purpose is to select a group of cells to export to a new file: a new file for each group of same rows having the same content in the first cell of the column.
Thanks
VBA: Select A Random Cell With In A Range?
Thanks for your help.
Here is the task at hand
Code a vba macro to:
1. Choose a range of cells.
2. Choose (n) random cells within the given range.
Thanks
-Straub
Edit by Moderator:
Please post Excel questions, in the Excel forum.
Thank you.
How: User Should Select Cell Range ?
simple question : how is the function call, to let a user select a cell range (by mouse)?
Example : if you add new data to a graphic, you will be asked to select the cell range with the new data by a small selector, where you may type in the coordinates or click on a field in the right to select by mouse.
Any hints are appreciated.
Delete Columns 2 - 24 In A Range Without Using Select
This is probably a dumb question, but how can I rewrite the code below WITHOUT using Select AND using numerical column names (such as Column(2) and Column(24)):
Code:
Columns("B:AE").Select
Selection.Delete Shift:=xlToLeft
Select Case For A Range Of Values
I need to program my mousedown event in such a way that if the x coordinate value falls between 0-1800 it should perform action1 ...for coordinates 1800-3600 action 2...and so on..
cant I hv something like a select case for a range of nos. like
select case (x)
case (0-1800)
//sfdbweg
case (1800-3600)
//jdvjidgf
end case
or do i have top rely on multiple if statements to keep checking ?
How To Select A Variable Range In Excel?
Does anyone know how to select a variable range in Excel?
Every book I have just says to select a range just do this
x1.Range("A1:A4").Select
That is cool and everything, but when you have a range that changes due to five different variables, that is way to much code to say
if (x<4) and (b>9) and (c<4) ...and so on
then x1.Range("A1:A200).Select
if (x<3) and (b>6) and (c<5) ...and so on
then x1.Range("C1:C200).Select
and so on.......
I need to figure out how to say
x1.Range(x,y).Select or something.
Am I overlooking an easy solution to this problem?
Select A Range Of Cells In Excel
Why do I keep getting this error when I try to select a range of cells in my worksheet?
Run-time error '1004':
Select method of Range class failed
Range("A13:K20").Select is the erroneous line.
Private Sub OptionButton1_Click()
Worksheets("Data").Activate
Range("A13:K20").Select
Selection.Copy
Worksheets("Test Plan Worksheet").Activate
Range("B27").Activate
ActiveSheet.Paste
End Sub
Select Cell Range Using For Loop
I'm trying to select a range of cells all on one line using the command
Range("C:G" & w).Select
where w is the for loop count variable. I keep getting a 1004 error so I replaced the C:G with one statement for each cell (C, D, E, F, G) which is ugly but works:
Range("C" & w).Select
...
Range("D" & w).Select
...
...
Range("G" & w).Select
...
Is there a way to modify my original code to achieve the same goal using about 30 less lines of code?
How Do I Select A Range Of Pages With A Defined Name?
Hi,
I've got a list of pages i want to select using VB, the list of pages is contained within a defined name ("PageSelector"). When i use this defined name to select one sheet - eg.
CODE Dim PageSelect As String
PageSelect = Evaluate("PageSelector")
Sheets(PageSelect).Select
Select Whole Data Range In Excel Vbe
Hi,
I have a code that I need to change, so it selects the whole data range instead of each region. I have a excel sheet which has 6 regions, but I want it pick up all the regions at once. If anybody can help me will appreciate it. Thanks
Public Sub CountHightSales()
Dim i As Integer
Dim j As Integer
Dim NumberHigh As Integer
Dim SalesCutOff As Currency
SalesCutOff = InputBox("What sales value do you want to check for?")
For j = 1 To 6
NumberHigh = 0
For i = 1 To 36
If Range("salesrange").Cells(i, j) >= SalesCutOff Then
NumberHigh = NumberHigh + 1
End If
Next i
MsgBox ("For region " & j & ", sales were above " & Format(SalesCutOff, "$0,000") _
& " on " & NumberHigh & " of the 36 months.")
Next j
End Sub
Select The N Largest Numbers In A Range
Been a LONG time since I've used VBA to develop functions for excel....and it seems that I'm very rusty. Not only that, I'm having trouble finding the answer.
I've got a range of cells.
A1:G:10
That are filled with numbers.
What I'd like to do is have three other cells (say H1,H2, and H3).
If the user were to put 4 in H1, 6 in H2 and 7 in H3 then in I1,I2 and I3 it would return the largest 4 numbers, the next largest 6 (after the 4) and the next largest 7 (after the 4 and the 6) respectively.
It hasn't been hard to use the following array formula to get the top 10:
=SUM(LARGE($A$1:$G$10,{1,2,3,4,5,6,7,8,9,10}))
and I'm sure I could probably modify it to get the next 5:
=SUM(LARGE($A$1:$A$100,{11,12,13,14,15}))
but how can I make those array sizes vary depending on user input??
thanks!
How To Set Print Range
Hello all
I have made print preview. But it was large that A4 size. I am notgetting that how to set print range and if it overflow then it goes tonext page.
And how to give next page for print preview and printer
Thanks
Amrit
Select Method Of Range Class Failed
Any idea what causes this message? Basically I am trying to run my print code from an access db through excel to print it. It was working well but when I tried to use it on different db tables I started getting this message.
Here is the code that is generating the message, although I'm guessing that the error is actually being fired somewhere else.
Code:
xl.Worksheets(strSheetName).Range("A1:A6000").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
Again, this was working until I tried callling it with different table data.
Any help would be much appreciated!
Quote:
Errors have been made. Others will be blamed
How Do I Select A Range Relative To The Active Cell?
I'm trying to select a range relative from the current active cell either using variables or constants, I don't want an absolute range because I want to loop the operation I'm trying to do several times in a spreadsheet...how do I do it? Thanks.
Select Method Of Range Class Error
I am writing a macro that will paste a set range to a variable workbook. I have no problem opening the workbook, but when I go to select the range I want to paste to on the new workbook, it give me a select method of range class error. Can anyone tell me what's going on? Here's the code:
Workbooks("workfile").Activate
Sheets("workpage").Range("a1:ap56").Copy
Workbooks(strwbkname2).Activate
Worksheets(strworkpage).Range("a1").Select <------ Error on this line
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
I appreciate any help you can offer.
Get User To Select A Range From A Non-active Workbook
I am using the RefEdit control on a form to get a user to pick a range of cells. However, the Excel book that is active when the form is shown is not the one that the range is to be picked from, and the RefEdit control does not seem to allow the user to change the active workbook.
Setting the ShowModal option on the form to false is not an option (Help says not to, and it is right(!) - Excel just freezes up).
The workbook from which I need the user to pick the range will be open, but I do not know its name, so can't switch to it before showing the form.
Any ideas about how to get around this without separately asking the user to specify the workbook & sheet they want to pick the range from?
Many thanks
Select Range Based On Cell Content
Hi there
I am new to VBA and am trying to write some code to select a range of cells dependent on the content and copy and paste it into another worksheet.
I have attached a sample file so that it is easier to see what i want to do. Basically, i want to select all the cells in column A that contain ADE, for example, and then for those copy all the relative cells in columns B, D, E and L.
The problem i have is that there will be a different number of ADE's each time i create the spreadsheet, and the first occurance of ADE will not always be in cell A4, so i need the code to be able to search the sheet for the first occurance and select the relevant number to copy.
Can anyone help?
Select Method Of Range Class Failed???
I keep getting the above error message on the following line of code:
Code:
myXLWrkSheet.Columns("A:A").Select
Here is the rest of my code so it makes more sense.
Code:
If aHit > 0 Then
Set myXLWrkBook = GetObject(f2)
Set myXLWrkSheet = myXLWrkBook.Worksheets(1)
numCols = myXLWrkSheet.UsedRange.Columns.count
strCol = "A" & numCols
With myXLWrkSheet
With .Rows("1:1").Font
.Bold = True
.Name = "MS Sans Serif"
.Size = 8.5
End With
End With
With myXLWrkSheet
With .Rows("1:1").Font
.Bold = True
.Name = "MS Sans Serif"
.Size = 8.5
End With
End With
myXLWrkSheet.Columns("A:A").Select ///// Error Msg Occurs \\
myXLWrkSheet.Selection.Insert Shift:=xlToRight
myXLWrkSheet.Range("A2").Select
myXLWrkSheet.ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[14],RC[1])"
myXLWrkSheet.Selection.Copy
myXLWrkSheet.Range("B2").Select
myXLWrkSheet.Selection.End(xlDown).Select
myXLWrkSheet.Selection.End(xlToLeft).Select
myXLWrkSheet.Range(Selection, Selection.End(xlUp)).Select
myXLWrkSheet.ActiveSheet.Paste
Else
With myXLWrkSheet
With .Rows("1:1").Font
.Bold = True
.Name = "MS Sans Serif"
.Size = 8.5
End With
.Columns("A:A").Insert Shift:=xlToRight
.Range("A2").FormulaR1C1 = "=HYPERLINK(RC[14],RC[1])"
' .Range("A2:" & strCol).Select ****** Why won't this line of code work *******
End With
End If
Select A Range Of Cells In Excel. Just Learning And Need Help.
Why do I keep getting this error when I try to select a range of cells in my worksheet?
Run-time error '1004':
Select method of Range class failed
Range("A13:K20").Select is the erroneous line.
Private Sub OptionButton1_Click()
Worksheets("Data").Activate
Range("A13:K20").Select
Selection.Copy
Worksheets("Test Plan Worksheet").Activate
Range("B27").Select
ActiveSheet.Paste
End Sub
Select Range Of Excel Cells In Flexgrid
I've connected an excel spreadsheet to a flexgrid but don't know the sql syntax to select a specific range of cells.
I know I can use SELECT * FROM [Sheet1$] but I need to limit the range further, ideally selecting the first four columns and tjhe full range of rows that contain data (the number of rows will increase as the spreadsheet expands).
Any help is much appreciated.
Select Method Of Range Class Failed
The below code exports data from Access 97 to Excel 97. I'm writting data to two different tab in the Excel file. One the first tab everything works as it should, including the freezepane. When the code reaches the line in the code to freezepane on the second tab I get the error indicated in the subject line of this message "Select Method of Range Class Failed". Not really sure where I'm going wrong with it but if I comment out the problem line everything works fine for both tabs. Sorry about the length of the code but I figured you should see all of it. I've highlight the problem line. Any ideas? Thanks.
CODESub ecDTR()
On Error GoTo ErrHandler
' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer
Dim highLight As Boolean
Dim sheetsPerBook As Integer
'CONSTANTS
Const aTab As Byte = 1
Const bTab As Byte = 2
Const aStartRow As Byte = 2
Const aStartColumn As Byte = 1
' set to break on all errors
Application.SetOption "Error Trapping", 0
' GENERATING OUTPUT FILE NAME
sOutput = "S:HWYREPORTSCOLAccountsEEmersonTEST EmersonDTR_" & Format(Now(), "yyyymmdd") & ".xls"
If Dir(sOutput) <> "" Then Kill sOutput
' CREATING EXCEL AND DB OBJECTS
Set appExcel = New Excel.Application
sheetsPerBook = appExcel.SheetsInNewWorkbook
appExcel.SheetsInNewWorkbook = 2 ' SETTING NUMBER OF WORKSHEETS IN WORKBOOK
Set wbk = appExcel.Workbooks.Add
appExcel.SheetsInNewWorkbook = sheetsPerBook
Set wks = wbk.Worksheets(aTab)
Set dbs = CurrentDb
sSQL = "select * from DTRexportDIV"
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
' ADDING COLUMN HEADERS TO EXCEL FILE
With wks
iCol = aStartColumn
iRow = (aStartRow - 1)
If Not rst.BOF Then rst.MoveFirst
iFld = 0
lRecords = lRecords + 1
For iCol = aStartColumn To aStartColumn + (rst.Fields.Count - 1)
.Cells(iRow, iCol) = rst.Fields(iFld).Name
.Cells(iRow, iCol).Interior.ColorIndex = 36
.Cells(iRow, iCol).Font.ColorIndex = 1
.Cells(iRow, iCol).Font.Bold = True
iFld = iFld + 1
Next
iRow = iRow + 1
rst.MoveNext
End With
With wks
.Rows("2").Activate
appExcel.ActiveWindow.FreezePanes = True
End With
' ADDING DATA TO EXCEL FILE
iCol = aStartColumn
iRow = aStartRow
highLight = False
With wks
If Not rst.BOF Then rst.MoveFirst
Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
For iCol = aStartColumn To aStartColumn + (rst.Fields.Count - 1)
.Cells(iRow, iCol) = rst.Fields(iFld)
iFld = iFld + 1
Next
iRow = iRow + 1
rst.MoveNext
Loop
End With
'AUTOFITTING COLUMNS
With wks
.Columns("A:AJ").EntireColumn.AutoFit
End With
'FORMATTING COLUMNS
With wks
.Columns("J:J").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("K:K").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("L:L").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("N:N").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("O:O").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("V:V").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("W:W").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("X:X").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("Z:Z").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AA:AA").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AF:AF").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AG:AG").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AH:AH").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AI:AI").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AJ:AJ").EntireColumn.NumberFormat = "mm/dd/yy"
.Cells(aStartRow, aStartColumn).Select
End With
' NAMING TAB(S)
With wbk
wks.Select
wks.Name = "Div_DTR"
End With
Set rst = Nothing
Set wks = Nothing
'*****************ADDING INFO TO SECOND SHEET***************************
Set wks = appExcel.Worksheets(bTab)
sSQL = "select * from DTRexportPOSKU"
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
' ADDING COLUMN HEADERS TO EXCEL FILE
With wks
iCol = aStartColumn
iRow = (aStartRow - 1)
If Not rst.BOF Then rst.MoveFirst
iFld = 0
lRecords = lRecords + 1
For iCol = aStartColumn To aStartColumn + (rst.Fields.Count - 1)
.Cells(iRow, iCol) = rst.Fields(iFld).Name
.Cells(iRow, iCol).Interior.ColorIndex = 36
.Cells(iRow, iCol).Font.ColorIndex = 1
.Cells(iRow, iCol).Font.Bold = True
iFld = iFld + 1
Next
iRow = iRow + 1
rst.MoveNext
End With
With wks
.Rows("2").Select
appExcel.ActiveWindow.FreezePanes = True
End With
' ADDING DATA TO EXCEL FILE
iCol = aStartColumn
iRow = aStartRow
highLight = False
With wks
If Not rst.BOF Then rst.MoveFirst
Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
For iCol = aStartColumn To aStartColumn + (rst.Fields.Count - 1)
.Cells(iRow, iCol) = rst.Fields(iFld)
iFld = iFld + 1
Next
iRow = iRow + 1
rst.MoveNext
Loop
End With
'AUTOFITTING COLUMNS
With wks
.Columns("A:BC").EntireColumn.AutoFit
End With
'FORMATTING COLUMNS
With wks
.Columns("N:N").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("O:O").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("P:P").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("S:S").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("X:X").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("Y:Y").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("Z:Z").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AE:AE").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AF:AF").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AI:AI").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AJ:AJ").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AO:AO").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AP:AP").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AQ:AQ").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AR:AR").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AS:AS").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AX:AX").EntireColumn.NumberFormat = "mm/dd/yy"
.Columns("AY:AY").EntireColumn.NumberFormat = "mm/dd/yy"
End With
' NAMING TAB(S)
With wbk
wks.Select
wks.Name = "Div_BOL_PO_SKU_DTR"
End With
'CLOSING AND SAVING NEW FILES
Set wks = Nothing
wbk.SaveAs FileName:=sOutput, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
wbk.Close SaveChanges:=False
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing
Set dbs = Nothing
Set rst = Nothing
ExitProcedure:
Exit Sub
ErrHandler:
Select Case Err.Number
Case Else
Call UnexpectedError(Err.Number, "ecSPNEM: " _
& Err.Description, Err.Source, _
Err.HelpFile, Err.HelpContext)
Resume ExitProcedure
Resume
End Select
End Sub
Use Variable To Select Range In Excel From Access?
is it possible to use a variable to select a range in excel from access? i am trying this:
Code:
Dim i As Integer
i = 21
'code here for setting up the worksheet, etc...
.Range("A1:C" & str(i)).Select
and that doesn't work...
it does work, however, if i use:
Code:
.Range("A1:C21").Select
Any ideas?
- one_eye
Edited by - cyclops_one_eye on 7/21/2004 11:20:33 AM
Select Method Of Class Range Failed
hi all!
I try to select a range of cells which depends of a variable and it returns me this error:
"Select method of class range failed"
below is the code:
Code: Range(Cells(10, 11), Cells(10 + indiceTableau, 12)).Select
i have tryed this too, but it didn't work and returned me the 1004 error
Code:ActiveSheet.Range(Cells(10, 11), Cells(10 + indiceTableau, 12)).Select
thanks for your future help!
How To Select A Range Of Cells, And Make A Sort Into?
Hi, my macro creates a Pivot Table.
The Table is created starting from a cell, that I can define as variables:
Code:Dim RowPivot As Integer
Dim ColPivot As String
ColPivot = "b"
RowPivot = 7
So I know where the table starts.
After, I look for the"Grand Total" string in the second row, to determine how the table is large (it depends by how many columns are selected).
Again, I look for the "Grand Total" in the first column to determine how the table is long.
For example:
Code:Columns(ColPivot).Select
Selection.Find(What:="Grand Total", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
y = ActiveCell.Row - 1
And similar for the x.
And now, I have to select only the data, excluding the first 2 and the last row.
The problem is that Sort needs a key (must be any elements of the last column) to make the sort, and this reference must be in "RyCx" format to specify Row and Column, in number.
look at this:
Code:Range("b9", "I29").Select
Selection.Sort Key1:="R9C9", Order1:=xlDescending, Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
ActiveWindow.ScrollColumn = 2
this makes a rigid selection and sort, and "R9C9" is manual inserted in the code, how to make it a variable?
How to convert the cell below the upper "Grand Total" in RyCx format?
Do you know a more elegant method to make this?
Many, thanks, Alessio
Edited by - skizzot on 6/18/2003 7:38:57 AM
|
TRACKER
