Thanks Will for the quick reponse. She is a complete novice so she needs something almost along the lines of an idiots guide to SQL. Ill have a look at the SAMS book
Thanks once again.
Graeme
Thanks Will for the quick reponse. She is a complete novice so she needs something almost along the lines of an idiots guide to SQL. Ill have a look at the SAMS book
Thanks once again.
Graeme
Hi All,
I have just been asked to recommend an SQL book to a collegue so that she can learn how to use SQL. She needs a book that will explain in simplified terms Unions and other more basic aspects of SQL.
TIA
Graeme
Hi Guys,
Brain has re-engaged I worked out the solution
Dim r As Integer
Sub RangeColSetup()
c = 1
r = 0
SheetName = ActiveSheet.Name
Worksheets("CL Reserves").Activate
Cells(2, 2).Activate
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Activate
c = c + 1
Loop
' r = r - 1
Cells(3, 1).Activate
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Activate
r = r + 1
Loop
ActiveWorkbook.Names.Add Name:="test1", RefersToR1C1:= _
Worksheets("CL Reserves").Range(Cells(3, 1), Cells(r, c))
End Sub
Display More
TIA
Graeme
Hi All,
My brain has gone into neutral....
I am trying to define a named range within a macro where I have identified the start and end of the area. what I cant seem to remember is how to code the last part of the add name range.
Sub RangeColSetup()
c = 1
r = 0
SheetName = ActiveSheet.Name
Worksheets("CL Reserves").Activate
Cells(2, 2).Activate
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Activate
c = c + 1
Loop
' r = r - 1
Cells(3, 1).Activate
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Activate
r = r + 1
Loop
area1 = ActiveSheet.Cells(3, 1)cells(r,c)
ActiveWorkbook.Names.Add Name:="test1", RefersToR1C1:= _
"='CL Reserves'!R3C1:"&"&" c , r"
ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:= _
"='CL Reserves'!R3C1:R634C55"
End Sub
Display More
TIA
Graeme
Hi Bearcub,
If you want to create a pivot table goto
Data|Pivot table and Pivot table chart
and follow the wizard.
HTH
Graeme
Hi MoretoLearn,
Am I correct in assuming that you are wanting to use Excel VBA to manipulate the data that you have extracted from the Access Db?
If so you might want to try and do a search in the Excel/VBA forum for posts pertaining to Arrays, there are a number of posts on them.
Once the code has finished where would the results be reviewed in Access or Excel?
Graeme
Hi Wkalo,
Firstly welcome to the board...
What version of Excel are you using? if you are using XP goto Tools|Macro|Security and select the Trusted Sources tab on the form. Make sure that both checkboxes are checked. This should do the trick
HTH
Graeme
Hi Schnett,
Firstly welcome to the board...
Could you provide a little more information such as:
1.)What external program are you getting the data from?
2.)Is the data in the same format each time you copy it (i.e. are the column widths of the data the same length)
This has been done before but there are a number of people that should be able to help with a bit more info.
GraemeH
Dreamboat
It's 5.2Mb the reason for this is the amount of data that it's using to produce the results I need. The spreadsheet is a work around as the Essbase database that I am getting the info from does not have the data in the right structure.
Does anyone know of a way of stopping this message from appearing.
I have large spreadsheet with 4 tabs that each have exactly the same comboboxes 6 in total. each time I change one of the criteria in one of the comboboxes I get the following error: "not enough systems resources to
display completely"
I have checked the Knowledge base and it suggests setting all of the spreadsheets to the same zoom level but this does not seem to have worked as I am still getting the same error.
TIA
Graeme
Hi All,
Just a quick question...
I have a memory hog of a spreadsheet that currently uses sumif to collate data from a datasheet and I was wondering if I use Dsum would the workbook become more efficient?
Dave / Andy
Thanks for the advice but it looks like I am going to have to build an access database to get around my data consolidation problem.
Thanks once again.
Graeme
Dave,
it is possible but my pc starts to strain abit and then eventually dies on me I thought that this was a memory issue so I have upgraded my memory to 1024K and still the pc locks up. There are +/- 10 sheets with 250 cells that have links to 22 workbooks (55000 links).
Personally I would never link this many spreadsheets because of this very issue but I have been landed with this.
Andy,
I have tried to update the links and it appears that I can... I have also noticed that if I do not have all of the files open that I cannot see all of the links it appears that I have exceeded the 256 limit.
Graeme
Hi All,
I have just been landed with a problem that I need to come up with a solution rather swiftly....
The problem is:
I have several files (22 intotal) all linked to a central spreadsheet. In the Central spreadsheet the layout is exactly the same as the other 22 spreadsheets. The only problem is that in every data cell there is a link to the 22 spreadsheets.
What I need to do is change the links so that they look at files in another directory. there are only two changes in the link that needs to be made 1 is the subdirectory (change the year)has to be changed and 2 is the filename contains a year as part of the name.
I have tried to use update links but I keep on getting the following error "Formula too Long".
I have also tried to update the links by selecting an area and preforming a find and replace but I keep on getting the same error.
Anyone have any ideas...
PS. the files all are sitting on a shared server.
TIA
Graeme
Stan,
Firstly welcome to the board.
You can allow several users to have access write access to a file by using Tools >Share workbook, check the allow changes. This will allow the users access to the same workbook the only draw back is that only one user can edit a particular sheet at once. In addition there are some functions that are disabled when a workbook is shared.
HTH
Graeme
Ranger,
Try using
Sheet1.UsedRange("O1").Calculate
Graeme
Ranger,
Check that on the Security|Trusted Sources that the Trust Access to Visual Basic Project is checked. This may be what is causing your problems.
Graeme
Ranger
Instead of calculating just the range("o1") try using calculate on a separate line. to force the entire workbook to calculate.
HTH
Graeme
Hi Bo-knows,
Instead of concatenating the text that is being broken up by excel have you tried using paste special there are a number of options that might be of use.
Or you could try edit the cell before pasting the data.
HTH
Graeme