|
It looks like you already specified which workbook to use when you started using the Range object of the xlsBookTs object you created. Or are you asking how to specify which worksheet to use?
In that case, you can either use the corresponding Worksheet object in the Workbook's Worksheets collection, or you can directly specify which worksheet to use in the Range address specifier (.Range("A" & NRow) in your code). This would look something like .Range("SheetName!A" & NRow).
|
|
|
|
|
I think we are getting close thanks to your help Dave.
I failed to mention the other workbooks that would be open due to trying to keep the code example to a minimum, however I am including a sample of the Module code below to show what other delairationa may be floating around in use at any given time.
Public xlsBookFds As Excel.Workbook
Public xlsSheetFds As Excel.Worksheet
Public xlsCellFds As Excel.Range
Public xlsBookTs As Excel.Workbook
Public xlsSheetTs As Excel.Worksheet
Public xlsCellTs As Excel.Range
Public xlsCellTs1 As Excel.Range
Public xlsBookMdrs As Excel.Workbook
Public xlsSheetMdrs As Excel.Worksheet
Public xlsCellMdrs As Excel.Range
In the format you mentioned;
.Range("SheetName!A" & NRow).
is the
! the code so the computer can seperate the sheet name from the cell location? That being the case I might hazard a guess that the code
.Range("BookName!SheetName!A" & NRow).
might be right.
Can thank you enough for your help so far Dave, looking forward to your reply.
Regards,
Frank 
|
|
|
|
|
*Can't thank you enough* I meant. Oops
|
|
|
|
|
frankiebaby2 wrote: is the !the code so the computer can seperate the sheet name from the cell location?
That's how Excel seperates worksheet names from cell names, yes.
|
|
|
|
|
I need help figuring out how to display the sql view of a query using VBA. I have an Access 2003 database that contains a little over 300 queries. Yikes!
I know in Access you can open a query in Design view and then change the view to SQL view so that you can actually see the Select statement. Instead of doing that more than 300 times to cut and paste the query statement, I would like to write a quick macro to display the information.
I was able to figure out how to loop through all my query objects and display the name, but I can't find a way to actually get at the sql view of the query. Below is the code I have:
Dim obj As AccessObject
Dim dbs As Object
Set dbs = Application.CurrentData
For Each obj In dbs.AllQueries
MsgBox "sql view: " & obj.FullName
Next obj
When I watch the obj data, there isn't any properties for the sql view. I thought this information would be in there. Am I misunderstanding what this object contains? It seems like I'm pretty close because I'm able to see the name of all the queries I have in my DB. What crucial piece of information am I missing?
Thanks,
FMPol
|
|
|
|
|
You may want to try looking at this[^]. If that doesn't work for you, I got more results from a carefully worded Google search, "vba access query sql text[^]". No, seriously, I had to go through about 15 search phrases to find the right combination of keywords.
|
|
|
|
|
Thanks Dave that was exactly what I needed. I think every google search I tried had 'view' in it and that must have been my big problem. I've posted the new code below:
Dim db As Database
Set db = CurrentDb
Dim curSQL As String
Dim qrynam As String
For Each qrydef In db.QueryDefs
qrynam = qrydef.Name
curSQL = qrydef.SQL
MsgBox "sql name: " & qrynam & Chr$(13) & "sql view: " & curSQL
Next
|
|
|
|
|
I am using the code below to successfully insert a new line just below every occurence of BEN*JE*ZZ*1041C0700X~ in the text file C:\myFlow_35.txt. My problem is, myString does not always = BEN*JE*ZZ*1041C0700X~; it will always start with BEN*JE*ZZ*
How do I give a value to myString to consider the other possibilities?
Private Sub btnTranslate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTranslate.Click<br />
MessageBox.Show("you are about to Translate file; make sure you have documented the original Reference Number")<br />
<br />
Dim refNum As String = Me.txtFileNum.Text<br />
Dim FileName As String = "C:\myFlow_35.txt"<br />
Dim myString As String = "BEN*JE*ZZ*1041C0700X~"<br />
Dim newString As New StringBuilder<br />
<br />
newString.Append(My.Computer.FileSystem.ReadAllText(FileName))<br />
newString.Replace(myString, myString & vbNewLine & "REF*LU*" & refNum & "~")<br />
My.Computer.FileSystem.WriteAllText(FileName, newString.ToString, False)<br />
<br />
MessageBox.Show("End of Translation ... Adios!")<br />
End Sub
Please gimme some ideas ... no pressure, but I have a 10 PM deadline today!
|
|
|
|
|
There is no wildcard character in string comparisons.
You're really confusing people when you ask questions. You said this code works as expected, but it doesn't do anything anywhere close to what you said it does.
Anyway, in a previous example that you posted, you had code in there to check if the string started with a certain substring, and in this case, that would be "BEN*JE*ZZ*". So, you'd do something like this:
If myString.StartsWith("BEN*JE*ZZ*") Then
....
|
|
|
|
|
Thanks for your input Dave. i dont mean to confuse you, but my code as posted does exactly what I need it to do, save and except the conditional values of myString. I am trying to learn and just looking for suggestions.
|
|
|
|
|
IvanIT wrote: save and except the conditional values of myString.
Well, that's kind of the important part! Don't just snip out parts of your code when you post snippets, at least not without a note saying you did so. People will look at your code and wonder what you're smoking.
|
|
|
|
|
I think there's no easy way. Since you use StringBuilder I think that one option is to convert it to string and then split by the known character sequence like:
Dim knownChars() As String = {"BEN*JE*ZZ*"}
Dim lines() As String = newString.ToString().Split(knownChars, StringSplitOptions.None)
after that you can loop through lines array and after each BEN*JE*ZZ* item, you'll add newline to the item after that at position 11.
|
|
|
|
|
I will gie this option a whirl and post the results here. Thanks again.
|
|
|
|
|
You're welcome
Hopefully it works.
|
|
|
|
|
I have already shown you how to use a regular expression to match a string using wildcards.
You are still using a StringBuilder, but you are not building any string. All that you manage to do with that is to copy all the string data one extra time.
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Thank you Guffa. I was suffering from one of those try and try again deal that ended up confusing me (and others). You have been a great help and I am learning. Thanks
|
|
|
|
|
How to sort data in DataGridView column who contain Date/Time records I click in header column but not sort correctly
|
|
|
|
|
|
So many ways to do this: dataview, sort from DB, sort on column, etc. Pick your poison.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
but I want to sort in Datagrid column because other way I want to sort another column
|
|
|
|
|
I know not to many people participate in VBA around here, but I'll post anyways
I'm trying to assign (in excel 2003, sp3) a string to the FormulaArray property of a range. The length of the string is 305, causing a crash because it is maxed over 255 characters (the error and cause is documentated poorly at http://support.microsoft.com/kb/213181, I say poorly because at the top of the article it states
This problem occurs when you try to pass a formula that contains more than 255 characters, and you are using the FormulaArray property in Visual Basic for Applications.
with no solution.
So I ask, does anyone here have any ideas as to a solution/workaround?
Thanks for the read / effort
|
|
|
|
|
Solution available at this site.[^]
Summary
''' If the long formula can be broken into parts, where the second part can
''' be replaced by a dummy function, this approach can be used...
Public Sub LongArrayFormula()
Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
theFormulaPart1 = "=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))-" & _
"MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-" & _
"(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+" & _
"{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),""""," & _
"X_X_X())"
theFormulaPart2 = "DATE(YEAR(NOW()),MONTH(NOW()),1)-" & _
"(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+" & _
"{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)"
With ActiveSheet.Range("E2:K7")
.FormulaArray = theFormulaPart1
.Replace "X_X_X())", theFormulaPart2
.NumberFormat = "mmm dd"
End With
End Sub
|
|
|
|
|
Hi,
I have a VB6 app with a VB.Net Interop user control ( a menu ).
When I click some option at the interop menu, it generates an event that is handled by the VB6 app to call a VB6 form. But after the VB6 form is called the focus remains at the interop usercontrol and then I need an aditional click to put the focus at the called form.
Thanks in advance.
|
|
|
|
|
Didn't VB6 have a SetFocus method on it's controls?? I'd say start by showing your form, the call SetFocus on the control you want the focus to move to.
|
|
|
|
|
Hi,
Yes...there's a setfocus at VB6 but it don't work at this situation.
I can see that the problem is related to modal forms and not to non modal forms
The following code works ok :
Private Sub InteropEventAtVb6()
MyVb6Form2.Show
End Sub
At the following code I need an extra mouse click to focus the form...
Private Sub InteropEventAtVb6()
MyVb6Form2.Show 1
End Sub
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
