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?

...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 such as ,.!"£$%^&*()_-+={}[]@':;~#<>?/|\¬` or maybe there is a regex for this very thing. For i = 1 To Len(a$) b$ = Mid(a$, i, 1) If b$ Like...

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

...4).Value)) .Update End With Application.StatusBar = "Entering temp pallet info: " & CInt((r / lastRow) * 100) & "%" Next r 'open a connection to the query table sqlStr = "SELECT...

...4).Value)) .Update End With Application.StatusBar = "Entering temp pallet info: " & CInt((r / lastRow) * 100) & "%" Next r 'open a connection to the query table sqlStr = "SELECT...

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

...at 2 but that will just add to the progress Application.StatusBar = "inserting keys for " & titleColumn.Cells(1, 1).Value & " : " & CInt((r / lastRow) * 100) & "%" Next Application.ScreenUpdating = True rs.Close conn.Close Set conn = Nothing Set rs = Nothing...