Search results for query: *

Hi, In VBA if I do Workbooks.Open fileName If the user has a file with the same name already open is there a way to catch the error and ask them to change the selection rather than returning a debug message.. that only lets you end or debug the method?

Wasnt sure how to approach this so just did this Function GetColNumByTitle(rng As Range, titleName As String) As Integer 'finds a title within row 1 of a range 'returns 0 if it cant be found Dim colCount, ColNum As Integer Dim titleStart, titleEnd, FindTitleCell As Range colCount =...

An approach I was probably going to take, which hopefully is quick.. would be to write a function to find the range by its title.. but then I need to know the col number for its address. E.g. GetColNumByTitle("SPCODE") returns 1 Then I can do srcRng.Cells(r, GetColNumByTitle("SPCODE").Value...

I have lots of files that all need sanitising and putting into an Access db. A lot of the files have the columns in different orders, I find all the relevant correct columns and then create a collection object called "filters" and add each column (range) to it. The do the union like below...

Actually I am just thinking that Range(Col1, Col7, Col5 ...) will work but I will have to test that later when I get time to work on this.

At the moment the union returns the cols in the original order e.g. Col1, Col3, Col5, Col7, Col10

Hi, I am using Application.Union to combine several 1 column ranges together. However I want to be able to order them in order the arguments are supplied to the method and not in the original order the columns were in. Is this possible? Example: Col1, Col2, Col3, Col4, Col5, Col6, Col7...

Thanks for the replies, great as always. Both suggested solutions look good I will try them out tonight. Thanks I take it VBScript.RegExp requires a reference?

Hi I came across this code for removing non alphanumeric from a cell value in VBA.. but I have not tested it yet. I was wondering if anyone knew of one that can be used for European languages as well. e.g. Taking Éé and Úú into consideration. Or is it better to do a like on symbols I dont want...

Oh and also changing the warehouses variant variable so that it is declared as Dim warehouses As Variant.. without the brackets.. because it only saw it as an empty variant that way. Sorry that will be my last comment on the matter. Just replying in case it helps anyone.

Ok think its sorted now. Changed the userform code so the if statement does the below If Not IsEmpty(warehouses) Then

No sorry its not entirely fixed because I will get a subscript out of range error when using Ubound on an array that has not got its dimensions set.. Silly me.. Stuck again now.

Sorted it now, thanks for the help in the right direction :) Function GetWarehouses() As Variant 'getting the warehouse list out of the database for use in the user form drop down list 'if no records are found then the function returns null Dim conn As ADODB.Connection Dim rs As...

Made a slight alteration to the function, I can see my error in not placing the GetWarehouses = null inside the else clause of the if statement. I wanted to return null if the recordset is empty is there a better way to be doing this? passing an empty array instead as the result of the...

Hi I have got the below code, I want a function that passes the values to my list box or combo box on a user form. I didnt like the idea of passing a recordset so i put it in an array.. problem is how do i reuse it as an array to populate the listbox?? the below doesnt work :( Function...

Hi, the below code seems to work ok.. It adds records to a temp table, compares them with the existing table using a query and then imports the ones that dont match as new records.. then deletes the records in the temp table. The only issue I seem to have at the moment is when deleting around...

Thanks Geoff, decided to change my work and do it the way you suggest. Here is some of my update code so far I am having a problem with the bit of code that is meant to delete the records from the temp table via the tempRs recordset. I get a runtime error 3219: operation is not allowed in this...

anyone got any suggestions on the above? I was thinking about doing multiple queries that only get 1000 rows a time from the database to try and cut down how much work needs to be done depending on the sort of the id's.

Sorry just to add to this further.. when insertcorrectkeys is run that is being done on 16600 rows because the raw data has duplicates that need to be replaced too. Thanks

Hi, I am asking for some help to improve the speed of the below subs. Specifically taking ~5mins in Excel when I run through a list of 5500 unique 18 digit text codes. I am using a query in an access dbase so that I can use these subs on different joined tables trying to keep things simple but...