VBForums - Office Development

Dilemma with Using a Form and textbox with Access

wengang — Sat, 18 May 2013 17:35:05 GMT

I think this is the right forum since the question is db related really.
I had to build this app in Access, because that is the only thing I'm allowed to code in at the office.

I created a form and put some labels and textboxes on it. One box is txtNotes, where I put notes on particular records (yes they are coming from the db, but the question is more about the object). Initially, I wanted to have a vertical scrollbar on this large text box, so that when the note was too large, the user could scroll down. I found that on the Access Form, the textbox has to have focus to show the scrollbar. That was fine. I set focus to it as the last step each time.

Then I encountered a new issue. I was using txtNotes.Text = RS("notes") and eventually, one of my notes was too long and I got an error telling me so. To fix this, I switched from txtNotes.Text to txtNotes.Value. That solved the "too long" problem, but created a new problem. Now when .SetFocus kicks in, the entire contents of the box is highlighted.

So the problem is, if I don't Set Focus, then I don't get scrollbars. If I do set focus, then my text gets highlighted. I could set .Value back to .text, but then some of my notes will be too long. (BTW, what is the limit, I haven't found it anywhere).

So that's it. Suggestions?
I should mention that the text in the box is a mix of English and Chinese, so any ideas that involve counting characters and acting accordingly will be hard to implement with any accuracy.

Thanks.

MsOf10 [RESOLVED] [Access] Importing csv files into Access through VBA

setsanto — Fri, 17 May 2013 18:05:10 GMT

Hello everyone,

I swear I'm going crazy. I have an import specification for csv files which, when I run it on a file through the GUI, works just fine. However, when I run it through VBA, for some reason it forgets that one column is supposed to be a Text column and makes it a Number column instead, thereby causing tons of errors.

My code is below. It *works* in the sense that everything runs properly, but for some reason the import specification for the CSVs does not run properly. The meaningless case switch is a place holder, as I will need to add more types of reports after I get the first working.

Code:

Sub ImportDE(Folder As Object)

    Dim db As DAO.Database

    Dim names As DAO.Recordset

    Dim Files As Object, file As Object, SubFolders As Object, subfolder As Object

    Dim ExString As Variant

    Dim check As Boolean

    Dim FileChange As String



    Set db = CurrentDb

    On Error Resume Next:   db.TableDefs.Delete "tblImport":   On Error GoTo 0

    db.TableDefs.Refresh

    

    Set names = CurrentDb.OpenRecordset("SELECT Old FROM DENames")

    

    Set Files = Folder.Files

    Set SubFolders = Folder.SubFolders



    For Each subfolder In SubFolders

        ImportDE subfolder

    Next

    

    With names

        Do While Not .EOF

            ExString = .Fields(0)

            For Each file In Files

                If InStr(file.Type, "Worksheet") > 0 Then

                    If InStr(file.Path, ExString & ".xls") > 0 Then

                        DoCmd.TransferSpreadsheet _

                            TransferType:=acImport, _

                            SpreadsheetType:=acSpreadsheetTypeExcel9, _

                            TableName:="tblImport_" & ExString, _

                            filename:=file.Path, _

                            HasFieldNames:=True, _

                            Range:="A:CT"

                        db.TableDefs.Refresh

                    End If

                ElseIf InStr(file.Type, "Comma Separated") > 0 Then

                    If InStr(file.Path, ExString & ".csv") > 0 Then

                        Select Case ExString

                        Case "Usage"

                            DoCmd.TransferText _

                                TransferType:=acImportDelim, _

                                SpecificationName:=UsageCSV, _

                                TableName:="tblImport_" & ExString, _

                                filename:=file.Path, _

                                HasFieldNames:=True

                            db.TableDefs.Refresh

                        End Select

                    End If

                End If

            Next

            .MoveNext

        Loop

    End With

    db.Close:   Set db = Nothing

End Sub

EDIT: For posterity, the answer is that I did not enclose the specification in quotation marks. It should be "UsageCSV".

[RESOLVED] How to pop up find box in Word with VB code?

Huldrich — Thu, 16 May 2013 17:14:43 GMT

How can I make pop up the find box avalaible manually through Edit / Find... under Word 2003 and stay there for further manual input? Is there some special command in With Selection.Find etc.?

Inserting HTML code (chart) into Excel spreadsheet.

umbata — Thu, 16 May 2013 15:17:20 GMT

Hello,

The HTML code below references a plot that is updated regularly... does anyone know how I can post the code into an Excel spreadsheet (MS Office 2010) to show the chart? Each time I open the spreadsheet, the chart would appear and be updated.

I have limited VBA familiarity ... would I need to use VBA code to insert? Any suggestions appreciated. Thanks so much.

Don

[RESOLVED] Limiting User's Interaction with Excel

Eugbug — Thu, 16 May 2013 15:02:14 GMT

I am writing a simple application which reads data from a GPS unit and stores the coordinates in a file. I am a novice when it comes to Excel automation and VB and am learning bit by bit how to do this! I have succeeded so far in writing a list of values into a column and after much "exception throwing" when an attempt is made to edit a cell, I set the interactive property of the Excel object to false to prevent any changes being made. This works fine but unfortunately this disables all interaction, so is it possible to prevent interaction with the cells of a worksheet but allow a user to scroll down through data (when this extends beyond the screen) ? I have tried to use the protect method and lock the cells in code, but this just causes Excel to complain about the sheet being protected and VB throws an exception.

MsOf10 Simple Array Script help

Surfboard — Wed, 15 May 2013 16:46:12 GMT

Hi there

Just need to insert 19 different arrays into my script, but still having trouble. Its unique serial numbers with a name to each.

Code:

SALGA = Array("0301/4535/0000", "0550/4535/0000") 'Salga

        

        Sec = Array("0301/4213/0000", "0550/4213/0000", "0700/4213/0000")

        

        TarW = Array("0101/4515/0000", "0401/4515/0000") 'Target Women

        

        TarY = Array("0003/4517/0000", "0401/4517/0000") 'Target Youth

        

        Train = Array("0211/4523/0000", "0501/4523/0000") 'Training

And i want to insert them into this:

Code:

                 myAry = Array("0003/3857/0000", "0101/3857/0000", "0201/3857/0000", "0301/3857/0000", "0401/3857/0000", _

                "0550/3857/0000", "0601/3857/0000", "0700/3857/0000", "0003/6063/0000", "0101/6063/0000", "0201/6063/0000", _

                "0301/6063/0000", "0401/6063/0000", "0550/6063/0000", "0601/6063/0000", "0700/6063/0000")

            For i = LBound(myAry) To UBound(myAry)

            If Range("B" & RowNumbr) = myAry(i) Then

                Qs = MsgBox("The current vote you created is allocated on Vehicle. Is This Correct?", _

                    vbYesNo + vbQuestion, "VALIDATE")

                If Qs = vbNo Then

                    Range("C" & RowNumbr) = "No"

            ElseIf Qs = vbYes Then

            Range("C" & RowNumbr) = "Yes"

        End If

    Exit For

End If

    If i = UBound(myAry) Then MsgBox "Vote Number Not found", vbCritical, "WARNING"

Next

This code works perfectly. My aim is to let the user insert the serial number via input box, then this code should run and display under which name it falls, and then display the name of the serial number, followed by an interaction with the MsgBox commands as seen in the example above. Then when no values are found, it should display the last message in the above script..

Is there any advise? I am very new to VBA scripts

[Excel] VBA and Registry Windows 7 Pro 64 bit.. a trick I am missing.

Putertechn — Wed, 15 May 2013 14:55:04 GMT

I manage a few kiosk style machines, network restricted and with all the protections we can heap on them.. well, they are all autologon, so have a user name and password in the registry. Unders windows XP I had no problem with reading the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon and the sub keys DefaultUserName and Default Password. and then even writing back a new password from my Excel list of machine and accounts.. but I have discovered that even though I read the above key, it seems that the StdRegProv is actually reading HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Winlogon.. and even on the Windows 7 machines, the values in use are in the first location.

The code I am using, right now just to read the values, is:

Const HKEY_LOCAL_MACHINE = &H80000002
Defpwd = "DefaultPassword"
Defname = "DefaultUserName"
On Error Resume Next
For a = 2 To 501
Cells(1, 12) = a
strhostname = Cells(a, 1).Value
Debug.Print strhostname
Call ResolveIP(strhostname)
If strhostname <> "Unreachable" Then
strhostname = Cells(a, 1).Value
Set objReg = GetObject("winmgmts:\\" & strhostname & "\root\default:StdRegProv")
strKeyPath = "SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon"
objReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, Defpwd, strValue1
Debug.Print strValue1
objReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, Defname, strValue2
Debug.Print strValue2
Cells(a, 8).Value = strValue1
Cells(a, 9).Value = strValue2
strValue1 = ""
strValue2 = ""
Else: Cells(a, 8) = "Unreachable"
End If
Next a

It actually specifies the correct location, but in Windows 7 it defaults to the second location. I am reasonably sure of this as I have gone in with Regedit and applied the settings to the WOW6432Node location and then this existing script "finds" the values. But changing the password in the Wow6432Node location does not get the password applied to the location that is read out in the autologon operation.

Anyone have any idea on how to get it to read out the right set of keys?

I have tried a lot of code pieces I have found on the internet, and so far they don't get me where I need to be.

Automated Emails

naminori — Wed, 15 May 2013 09:43:45 GMT

Okay total Noob here so please bare with me.
I used this video to write what I�ve got.
http://www.youtube.com/watch?v=Z-hsCUl1qW8

I�m trying to send email notifications, only once, if J14 = <7.0, then stop the emails.
And then wait for J14 = >6.9, send another email, stop emails and wait for J14 to = <7.0 again.
Right now it just keeps emailing every 20 minutes when J14 is updated by a web query.

Any help would be greatly appreciated.
Thanks

Here�s what I�ve got.
==============================================================
Private Sub Worksheet_Calculate()

Control1 = Range("I11").Value

Trigger = Range("J11").Value

HighestRecordedPeriod = Range("J14").Select
If HighestRecordedPeriod < Trigger And Control1 = "MAIL" Then

Dim ADDRESS As String, SUBJECT As String
Dim BODY As String, HYPER As String

ADDRESS = "myaddress@hotmail.com"
SUBJECT = "This is an Alert - The period has reached " & Range("J14") & " seconds"
BODY = "test"

HYPER = "mailto:" & ADDRESS & "?Subject=" & SUBJECT

ActiveWorkbook.FollowHyperlink (HYPER)
Application.Wait (Now + TimeValue("0:00:15"))
Application.SendKeys "%S"

Range("I11").Select
ActiveCell.FormulaR1C1 = "MAIL"

End If
End Sub

Excel (mac) macro redirect to webpage extremely slow > HUH?!

PeterJKorst — Mon, 13 May 2013 17:26:13 GMT

Dear Experts,

I am running into an extremely annoying and strange problem. I hope someone can help me out with this, it drives me crazy by now! :s

I have the following macro:

Code:

Sub StuurNaarBoekhouding()



'Are you sure?

Antwoord = MsgBox("Sure?", vbOKCancel + vbExclamation, "WATCH IT!")

If Antwoord = vbCancel Then Exit Sub



'workbook save

ThisWorkbook.Save



'get modified date

Dim sLMD As String

    On Error Resume Next

    sLMD = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")

    If Err = 440 Then

        Err = 0

        sLMD = "0"

        ' not found so ask to save

        MsgBox "Not saved yet. Save it first.", vbOKOnly + vbCritical

        Exit Sub

    End If



'get filename

filelocation = ActiveWorkbook.FullName



'get extra info for url

Dim kostenplaats As String

kostenplaats = Worksheets("SHEETX").Range("C9:C9").Value

If kostenplaats = "" Then

    MsgBox "The extra value for the url is not set, fill in first.", vbOKOnly + vbCritical

    Exit Sub

End If



'create s code

Dim s As String

s = "coded_string_for_security_reasons"



'the url base

Dim url As String

url = "http://www.website.com/"



'UNTIL HERE, THE CODE WORKS FAST AS LIGHTING (tested by throwing an msgbox here)

'THE STEP DOWN HERE TAKES TWO (!!) MINUTES



'send to url

ActiveWorkbook.FollowHyperlink Address:=url + "?code=" + s + "&last_changed=" + sLMD + "&extra_info=" + kostenplaats + "&p=" + filelocation



'workbook save without saving again (so that saved last time info doesnt change)

ActiveWorkbook.Saved = True

ActiveWorkbook.Close SaveChanges:=False

End Sub

Now on windows, this piece of code works perfect. But on my mac (osx 10.8.3) office excel 2011 it does work, but the 'followhyperlink'-step takes 120 (!!) seconds to execute. This is extremely slow (on the windows it takes only 0.5 seconds or so).

What happens? How can I solve this problem?

I really hope that someone can help me out. Would be very very much appreciated!

S

Using CTPFactory and CTPFactoryAvailable methods to interact with the ribbon UI

tfitzhardinge — Mon, 13 May 2013 06:19:22 GMT

Hi

I am using NetOffice and Visual Studio 2010 Express to develop word COM addins.

Very simple I want to show a custom task pane (CTP) when a check box is ticked in the ribbon UI.

NetOffice cannot directly using the standard custom task pane objects that are available in the visual studio express. It has to rely on CTPFactoryAvailable method to show the CTP.

The following example shows the CTP upon loading up word however I cannot make it more dynamic to only show the CTP when the checkbox is ticked.

Does anyone have any ideas?

Adding the ICustomTaskPaneConsumer to the Implements
Office.ICustomTaskPaneConsumer

And use the CTPFactoryAvailable sub to create the custom task pane.

Public Sub CTPFactoryAvailable(ByVal CTPFactoryInst As Object) Implements NetOffice.OfficeApi.ICustomTaskPaneConsumer.CTPFactoryAvailable
Dim ctpFactory As Office.ICTPFactory = New Office.ICTPFactory(_wordApplication, CTPFactoryInst)
Dim taskPane As Office._CustomTaskPane = ctpFactory.CreateCTP(GetType(Addin).Assembly.GetName().Name + ".SampleControl", "NetOffice Sample Pane(VB4)", Type.Missing)
taskPane.DockPosition = MsoCTPDockPosition.msoCTPDockPositionLeft
taskPane.Width = 300
taskPane.Visible = True
_sampleControl = taskPane.ContentControl

MsOf10 [RESOLVED] Compare rows and copy to new sheet (VBA)

Radjesh Klauke — Sun, 12 May 2013 12:43:15 GMT

Hi, me again :P

If Value in Column A > then Column B then copy the row to a new sheet (for example sheet2). Any chance someone knows how to code it in VBA or willing to give me a kickstart?
The sheet isn't always the same and contains 100's or rows, that's why I want to automate it. Had enough of it :P

Thanks in advance.

MsOf10 [RESOLVED] Set minimum value Column A (VBA)

Radjesh Klauke — Sun, 12 May 2013 07:22:07 GMT

Hi,

I have a lot of values in column A. A lot of them are below a value of 10, they all should have a minimum of 10. Anyone with a quick VBA solution?

I tried:

Code:

Sub test()

    Dim c As Range

    Application.ScreenUpdating = False

    For Each c In ActiveSheet.UsedRange.Columns("A")

        If Trim(c.Value) > 0 And c.Value < 10 Then

            c.Value = 10

        End If

    Next c

    Application.ScreenUpdating = True

End Sub

It should skip empty cells if possible.

Thanks in advance.

MsOf10 Updating an Excel doc that is already opened from another already opened Excel doc

Genghis — Sat, 11 May 2013 00:31:11 GMT

Hello,

If I have two to four excel documents already opened and I want to send updates to one of the other excel documents from one I am working in.

How can I get a list of excel documents that are already opened, and select one to get the handle of it so I can send information to it? I am looking for code that is dynamic enough that I don't have to hardcode a file name in it and I can use it to select one of the already opened documents that I may not know the location of in my system.

I hope this is clear enough for someone to help.

Thanks in advance,
Genghis

SaveAs Macro Enabled Workbook Excel 2010

Esham — Fri, 10 May 2013 13:05:53 GMT

We've been using the same macro for years on the older Office versions. Now we have Office 2010 and the macro still works except for saving the workbooks. I've attached screen shots of the errors. Here is a quick breakdown of what the macro does:

1. Opens a template file (which already has macros inside it).
2. Opens some other excel files and manuiplates and transfers the data to the template file.
3. Saves the template file as a new workbook with a unique name.

The problem with the new Office 2010 is that I cannot figure out how to write the code to save as a macro enabled file?? Here is the code snipit where I am trying to save the file and where I get the errors. The error comes after the "MasterWb.SaveAs...." line.

Any help would be greatly appreciated!!

Code:

Workbooks.Open(Filename:="H:\Maintenance\Schedule Template\Rochelle Weekly Schedule Individual Master.xltm").RunAutoMacros Which:=xlAutoOpen

DO ALL THE DATA MANIPULATION STUFF

fileSavePath = DiscWB.Path & "\"

fileSaveName = "Contract " & Format(Now + 3, "mm-dd-yyyy") & ".xlsm"

MasterWb.SaveAs Filename:=fileSavePath & fileSaveName

Attached Images

Return the column position of found value within a range

scoobster — Fri, 10 May 2013 08:13:27 GMT

Hi
I have a named range that sits in range D1 to H1. My program uses the FIND method to check that a value exists in the named range. When the value exists then I want to return the position within the range but the following is giving me the position on the worksheet.

Code:

                DiscountColumn = Sheet4.range("ExportHeadings").Find(What:=DiscountString, MatchCase:=False).Column

So if the value is found in the first position within the named range then I expect the 1 to be returned and not the value 4.

I know I could loop through the range to find the value as it's a small range but I read that the FIND method is much more efficient.

Is it possible to return the position from within the named range?

Thanks