Visual Basic for Applications (VBA) Forum

how to assign a macro to an excel cell(click)

Wed, 25 Nov 2009 12:25:50 Z

Hello , thanks for reading

I have recorded a macro named "mycalendar".

Now i want it to run when ever user clicks on any cell ranging A1:A50.

please help.

thank you.

personal function not recalcing

Wed, 25 Nov 2009 13:00:19 Z

I created a function that returns the worksheet name, but for some reason when i open the file the functions cells all show 0, if i recall they still show 0, if i go to the cell hit f2 and enter the formula works or if i change the cell that the formula refers to it works. Whats up?

Public Function GetSheetName(codeName As String)
Dim wks As Worksheet
codeName = WorksheetFunction.Proper(codeName)
For Each wks In ActiveWorkbook.Sheets
If wks.codeName = codeName Then
GetSheetName = wks.Name
Exit Function
End If
Next

End Function

McC

Is it possible to pause a Macro recording in Excel 2007?

Wed, 25 Nov 2009 21:34:17 Z

Dear all,

I've just started learning VBA. Currently I'm reading a chapter about Macro recording in Word and Excel (Office 2007). Whenever I record a Macro in Word it is possible to pause the recording (but not stop it definitly) in order to avoid recording several actions which are irrelevant to our Macro. Yet, I didn't find any Pause button for recorded Macros in Excel. Is there any?

Thanks in advance
Dariyoosh

MDE from Access 2007 doesn't work?

Thu, 03 May 2007 20:04:02 Z

Hi There,

I have created a small application in Access 2007, from which when I make an MDE (as the runtime for Access 2007 isn't yet available) the Access 2003 Runtime pops up an error saying that it was created in a newer version.

Has anyone else experienced this?

Also, in an attempt to work around this, I created an MDB from 2007, which I then opened in 2003 and made an MDE which works fine, except every time I open the MDE that works, it pops up an input box (almost like a prompt, but nothing I designed) asking for an input value for TempVars!SwitchboardID. I have the MDE configured to open the switchboard automatically.

I'm at a bit of a loss here, any chance that some of you have experienced this?

Many Thanks

Accessing data from a query, binding to text boxes on form (Access)

Tue, 24 Nov 2009 22:55:09 Z

For part of an assignment we have to query the database using one of two primary keys (it is for a fictional help desk). Those keys are User ID (the person the created the support request) and Problem reference.

The query's are executed from a form, and when executed they open another form - which is where I am trying to get the data displayed.

The bit that I am stuck on is grabbing the relevant data from the executed query and transferring it to the form. Because I know that I can transfer the data used in the first form and transfer it to the form. But how do I get the data from a query to be populated into the respective fields on the form?

Bug in GetOpenFilename

Wed, 25 Nov 2009 09:05:43 Z

Here is the code:

Dim sDataFile As Variant
Dim oDataFile As Workbook

If IsEmpty(Range("F1")) Then
  sDataFile = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", MultiSelect:=False)
Else
  sDataFile = Range("F1").Value
End If

If sDataFile <> False Then
  Set oDataFile = Workbooks.Open(sDataFile)
Else
  MsgBox "You must select a file."
  End
End If

If you run that code (I'm running Windows 7, Excel 2007), you'll get a dialog box to choose the file but when you choose the file it actually opens it and closes the file that contains the macro! What the heck?!?

Assigning recordset opened from query to recordset object

Wed, 25 Nov 2009 14:41:22 Z

The following source code works perfectly, except for the bold source code line
Set myRecordset = qryPMNames.OpenRecordset(dbOpenDynaset)
which throws the error: "Run-time error '13': Type mismatch" independent of the Open-Type (here "dbOpenDynaseet") used.
Do I overlook something simple regarding the assignment to the Recordset object?
Do I need to use a special Recordset type, e.g. DAO, depending on the setting of the MS Access 2007 database?

--------------------------------------------------------------------------------------
Dim i, nbPMs As Integer
    Dim qryPMNames, qryPMName_MRs As QueryDef
    Dim myQueries As QueryDefs
    Dim myDB As Database
    Dim myRecordset As Recordset
    Dim strPMName, strQueryName As String

Set myDB = CurrentDb
        With myDB
            Set myQueries = .QueryDefs
            Set qryPMNames = .CreateQueryDef("qryMissingResponsesPMnames", "SELECT PM FROM qryAllMissingResponses_withPM GROUP BY PM")
            nbPMs = qryPMNames.OpenRecordset(dbOpenDynaset).RecordCount
            qryPMNames.OpenRecordset(dbOpenDynaset).MoveFirst
            Set myRecordset = qryPMNames.OpenRecordset(dbOpenDynaset)

Read email from outlook Inbox and save body to access table/field

Wed, 25 Nov 2009 17:43:02 Z

What I would like to do is have an event that will go to the current users inbox and check for unread mail. I would like to add the body of the mail to a field so i can do some string manipulation to pull the data out of it that I need. I found this code and I tried chaning it to work for me, but its not working. I dont know anything about Outlook integration when it comes to VBA.

Also, is there some knowledge base or something where I can read up on this and understand it better?

Private Sub Command0_Click()
Dim TempRst As DAO.Recordset
Dim rst As DAO.Recordset
Dim OlApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim InboxItems As Outlook.Items
Dim Mailobject As Object
Dim db As DAO.Database
DoCmd.RunSQL "Delete * from tbl_outlooktemp"
Set db = CurrentDb


Set OlApp = CreateObject("Outlook.Application")
Set Inbox = OlApp.GetNamespace("Mapi").getdefaultfolder(olFolderInbox)
Set TempRst = CurrentDb.OpenRecordset("tbl_OutlookTemp")
'
Set InboxItems = Inbox.Items
'
For Each Mailobject In InboxItems
    If Mailobject.UnRead Then
    With TempRst
        
        .AddNew
        !Subject = Mailobject.Subject
        !To = Mailobject.To
        !Body = Mailobject.Body
        !DateSent = Mailobject.SentOn
        .Update
        Mailobject.UnRead = False
    End With
End If
Next

Set OlApp = Nothing
Set Inbox = Nothing
Set InboxItems = Nothing
Set Mailobject = Nothing
Set TempRst = Nothing
End Sub

Create an array from a table row

Wed, 25 Nov 2009 00:10:02 Z

Hi all,

I have a row in a table (access 2007) that has 9 date columns. I would like to grab those dates and put them into an array. My goal is to create a function which will return the min value from all the dates in the array. Does anyone have an example they could share? I'm accessing the row in the table via recordset.

Thank you for any help with this! :)

excel vba calls c dll path

Sat, 08 Mar 2008 07:51:52 Z

Private Declare Function AddFun Lib "C:\myfolder\MyDLL.dll" ( ByVal a as Long, ByVal b as Long) As Long

---

Hello,

My dll is written by C++. I use it in Excel 2003 VBA. I got "C:\myfolder\MyDLL.dll" not found error while calling it.

It works fine when I add the folder to system PATH. But in my case, I like to use absolute path, and not to change PATH in users' machine.

any idea? Thanks much!

-ZZ

how to change the caption of PowerPoint 2010 Window

Wed, 25 Nov 2009 08:37:15 Z

Hi,

Even in PowerPoint 2007, the caption property of Window was read-only (dont know why) but user can still change the caption by setting

application.caption = "changed caption value".

But in PowerPoint 2010, even this does not work. it does not change the title of the Active Window.

Is there any way in PowerPoint 2010 to do it?

Thanks,

Salil

Issue in MSO shapes object for MSO 2007 SP2 installations

Thu, 19 Nov 2009 05:52:13 Z

We have a VBA based application that run with Excel. We are facing an issue while rendering objects like menus and tabs. The code Shapes.AddShape(msoShapeRectangle) is not working as expected while comparing to earlier MSO 2007 SP1. Used MSO shapes object to
paint the menus. Is there any change has been done on this from SP1 to SP2 ? Kindly suggest on this.

Excel 2003 Limit to Get and Set data using Range.Value

Thu, 19 Nov 2009 17:04:48 Z

I am trying to copy data from one row in a sheet to another row in the same sheet but am getting a 1004 application (or object) defined error on this line of code:

Worksheets(STOICH_SETS).Range(STOICH_FIRST_LETTER & comp_row_index & ":" & STOICH_LAST_LETTER & comp_row_index).Value _
= Worksheets(STOICH_SETS).Range(STOICH_FIRST_LETTER & row_index & ":" & STOICH_LAST_LETTER & row_index).Value

It works fine if I remove the 4 cells in each row that contain over 1600 characters, but as soon as I introduce any one of them back in I get the error. It also works fine if I just move those larger cells separately. Is there a limit to what I can copy this way?

Thanks

"Document1" automatically closing

Thu, 19 Nov 2009 00:07:39 Z

Greetings! I've encountered a problem with a VBA macro I'm working on in Word... I found a workaround and figured it would be best to post for any & all.

This macro uses the AutoExec sub to load some CustomXMLParts from an installed template/add-in. Basically I run a loop over the Templates collection and when it finds the one with the correct name, a call to OpenAsDocument is made... customXMLParts are grabbed, items are added to a collection, then the document is closed.

The problem is, when word is started from scratch (e.g. double click the word icon) it starts a Document 1. When there are no changes to this document, and OpenAsDocument is called ( as will happen with the AutoExec sub ), this has the effect of closing Document 1. After the template has been closed subsequent calls to ActiveDocument returns the error: "This command is not available because no document is open

Sub AutoExec

Debug.print ActiveDocument.Name ' Document 1

' load the template

For Each oTemp In Templates
If oTemp.Name = "TheTemplate.dotm" Then Exit For
Next

' open the template as document
Set oDoc = oTemp.OpenAsDocument()

Debug.print ActiveDocument.Name ' TheTemplate.dotm

oDoc.Close SaveChanges:=wdSaveChanges

Debug.print ActiveDocument.Name

' EXPECTED: Document 1
' RESULT: This command is not available because no document is open<br/>

End Sub

As a workaround, I've added the following before opening the template:

If Len(ActiveDocument.Path) = 0 Then
' un-saved document
Set rng = ActiveDocument.Range(0, 0)
rng.Text = " "
rngSet = True
End If

And the following after the OpenAsDocument

If rngSet = True Then
' undo the single space addition & remove object reference
rng.Text = ""
Set rng = Nothing
End If

Transfer data from SQL to text files

Wed, 25 Nov 2009 08:31:56 Z

Hi..

I am completely new to SSIS..

Can i know how to transfer tables from SQL Database to Text files(.txt)

Hide/Disable Select Multiple Items CheckBox for a pivot filter

Wed, 25 Nov 2009 08:15:33 Z

I need to hide/Disable select Multiple Items check box in Pivot Filters
How can i achieve this through VBA or through MS office settings?

Thanks Dileep

EventLog

Fri, 20 Nov 2009 11:29:51 Z

Hi Gurus,

Please let me know how to write logs to EventLog of the system through VBA.

Cheers.

Powerpoint Build in command not working in 2007.

Tue, 17 Nov 2009 21:06:39 Z

Hello All,

I am in the process of porting a DLL file (COM Add-In) from Powerpoint 2003 to 2007.

The idea is to make sure all the commands work in 2007. I am not modifying the ribbon etc.

Following code add a Button to a custom toolbar which is for the Subscript button. It works perfectly fine in 2003 but not in powerpoint 2007.
Set mobjCmdBar = Application.CommandBars.Add(Name:="CustomCommandBar")
mobjCmdBar.Visible = True
mobjCmdBar.Controls.Add Type:=msoControlButton, Id:=58

Icon is properly shown in 2007 however it just does not Subscript the selected text. Nor does it give any error in PP 2007.

Any ideas or suggestions?

Thanks.

help out! docmd.runsql "..." get error 2051

Tue, 24 Nov 2009 22:01:23 Z

Using VBA in Excel 2002 , Access 2003 created a report tool .runs well.
until last week we update access2003 to 2007.(excel still 2002).
get error for grammar :" docmd.RunSQL "update .... " "
Error message show "The RunSQL action was canceled."

Reference:
1. Visual Basic for Application
C:\Program Files\Common Files\Micorsoft Shared\VBA\VBE6.DLL
2. OLE Automation
C:\WINDOWS\SYSTEM32\STDOLE2.TLB
3. Microsoft Access 12.0 Object Library
C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE12\MSACC.OLB
4. Microsoft Excel DAO Object Library
C:\Program Files\Common Files\Micorsoft Shared\DAO\DAO360.DLL
5. Microsoft ActiveX Data Objects 2.8 Library
C:\Program Files\Common Files\system\ado\msado15.tlb
6. Micorsoft Forms2.0 Object Library
C:\WINDOWS\SYSTEM32\FM20.DLL
7. Micorsoft Office 12.0 Object Library
C:\Program Files\Common Files\Micorsoft Shared\OFFICE12\MSO.DLL
8. Microsoft Excel 10.0 Object Library
C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\EXCEL10.EXE

DoCmd.OutputTo does not work from a library database using acFormatSNP or acFormatPDF

Tue, 08 Jul 2008 21:54:40 Z

I have a library database that contains a function designed to output reports in snapshot format using docmd.OutputTo with the acFormatSNP option. I developed the library in Access 2003 along with several front-end databases that reference the library and use the function to output reports that are located in the front-end database.

This all works in Access 2003, but not in 2007. I get Error #2059 saying that the report cannot be found. Calling OutputTo directly in the front-end database works fine.

I have also tried this with the new SaveToPDF add-in and the acFormatPDF format. Same result - works locally but not in the library.

Extremely simplified sample code follows:

Database1 (front-end)

Form1

Private Sub Command1_Click()

Output_Local

End Sub

Private Sub Command2_Click()

Output_Library

End Sub

Module1

Function Output_Local()

DoCmd.OutputTo acOutputReport, "Report1", acFormatSNP, CurrentProject.Path & "\Report1.snp", True

End Function

Database2 (library)

Module1

Function Output_Library()

DoCmd.OutputTo acOutputReport, "Report1", acFormatSNP, CurrentProject.Path & "\Report1.snp", True

End Function

In Database1, I set a reference to Database2.

In Form1:

Command1 outputs the report in snapshot format

Command2 gives the following error:

Run-time error '2059':

Microsoft Office Access cannot find the object 'Report1'.

The 'Debug' button takes me to the OutputTo statement in the library database.

I can't come up with any explanation other than a bug in Access 2007. It works fine in 2003. The VBA help docs in 2007 still claim that it should be able to output a report in the current database from a library database. The actual production code is a bit more complex, so I don't want to have to copy it out of the library into a bunch of separate databases.

Any ideas?

Thanks

SaveAs method appending .xls to filename

Fri, 20 Nov 2009 18:43:46 Z

Hi, am hoping someone more knowledgeable than I might be able to shed some light on an Excel / VBA issue I've encounted:

I've got some VBA code which saves a workbook but uses the extension plan instead of xls. E.g. "site1.plan" as opposed to "site1.xls"
The code runs fine on my development PC but when run on a remote server via an RDP session, .xls is appended onto the filename, e.g. site1.plan.xls

I'm struggling to understand why this is happening.

If I save the file using File|Save As, the filename is correct and .xls isn't added.
Using the SaveAs method and an extension that isn't .plan, e.g, .test or .abc works fine.
I'm using Excel 2002 (10.6501.6626) SP3.

I've run through a Process Monitor trace of Excel's file system and registry activity to see if there was any suspicious calls but nothing jumped out. No references to the original filename without the .xls extension for example.

The same code has been working correctly for the past 5+ years and this problem has only occurred after loading Excel (and the workbook) onto a new server.

Any ideas anyone?

Cannot fully close Exce;

Tue, 24 Nov 2009 08:36:11 Z

Hi all,

I have an access app which open up a query in Excel then I tweak the excel with vba and finally close it. However, when I close it I look at the processes running still and there's still an Excel instance. How can I completely close Excel. Here's a sample of my code.

Function outputToExcel()

Dim myQuery As QueryDef
Dim openTable As TableDef
Dim dbCurrent As Database
  
Set dbCurrent = DBEngine.Workspaces(0).Databases(0)

DoCmd.RunSavedImportExport "Import-table"   'Imports table
DoCmd.RunSavedImportExport "Export-qry"     'Export qry to Excel

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWs As Excel.Worksheet

Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("c:\temp\output.xlsx")
Set xlWs = xlWB.ActiveSheet

Dim rsState As DAO.Recordset
Set rsState = dbCurrent.OpenRecordset("qry_2")

xlApp.DisplayAlerts = False
xlWB.SaveAs "C:\temp\output.xlsx", FileFormat:=51
xlApp.DisplayAlerts = True

rsState.Close
Set rsState = Nothing

Set myQuery = Nothing
Set openTable = Nothing
dbCurrent.Close
Set dbCurrent = Nothing

xlWB.Save
xlWB.Close , "C:\temp\output.xlsx"
xlApp.Quit
Set xlWs = Nothing
Set xlWB = Nothing
Set xlApp = Nothing

MsgBox "Completed"

End Function

Does anyone see what I need to close? Thank you!

Excel Macro Help

Tue, 24 Nov 2009 16:51:44 Z

Need help creating a macro that will find a value in a sheet and store it as a variable. Then also store values from cells in variables that are adjacent to the cell found in the "find".

Help with Progress Bar - getting error 7952 (acSysCmdUpdateMeter)

Wed, 04 Nov 2009 08:00:45 Z

Hi All,

I have an access app and I'm trying to incorporate a progress bar as a function loops through a query recordset.

I'm able to initiate the progress bar by:

syscmd acSysCmdInitMeter, message, total#

but when I try to increment the progress bar:

syscmd acSysCmdUpdateMeter, progress#

I get error: '7952 You made an illegal function call'

Any ideas how to fix this?

Thank you!

Video Capture with Audio

Tue, 24 Nov 2009 16:31:21 Z

Hi,
I'm new to the world of programming and am trying to put together a video capture and playback programme. The capturing of video works fine, however, I am capturing from a DV camcorder via firewire and the audio that is captured is taken from the microphone on the computer rather than from the camcorder. does anyone know how to rectify this, any help would be massively aprreciated. It should also be noted I am making this in excel 2002, an extract from the code is shown below,
many thanks,

Private Declare Function GetSystemMetrics Lib "user32.dll" (ByVal nIndex As Long) As Long
Const SM_CXSCREEN = 0
Const SM_CYSCREEN = 1
Private Declare Function mciSendString Lib "winmm.dll" Alias "mciSendStringA" (ByVal lpstrCommand As String, ByVal lpstrReturnString As String, ByVal uReturnLength As Long, ByVal hwndCallback As Long) As Long
Private Declare Function mciGetErrorString Lib "winmm.dll" Alias "mciGetErrorStringA" (ByVal dwError As Integer, ByVal lpstrBuffer As String, ByVal uLength As Integer) As Integer
Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Declare Function capCreateCaptureWindow Lib "avicap32.dll" Alias "capCreateCaptureWindowA" (ByVal a As String, ByVal b As Long, ByVal c As Integer, ByVal d As Integer, ByVal e As Integer, ByVal f As Integer, ByVal g As Long, ByVal h As Integer) As Long
Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Integer, ByVal lParam As Any) As Long
Global Const ws_child As Long = &H40000000
Global Const ws_visible As Long = &H10000000
Global Const WM_USER = 1024
Global Const wm_cap_driver_connect = WM_USER + 10
Global Const wm_cap_set_preview = WM_USER + 50
Global Const WM_CAP_SET_PREVIEWRATE = WM_USER + 52
Global Const WM_CAP_DRIVER_DISCONNECT As Long = WM_USER + 11
Global Const WM_CAP_DLG_VIDEOFORMAT As Long = WM_USER + 41
Global Const WM_CAP_SEQUENCE = WM_USER + 62
Global Const WM_CAP_FILE_SAVEAS = WM_USER + 23
Global Const WM_CAP_DISCONNECT As Long = 1035
Global Const WM_CAP_GET_STATUS As Long = WM_USER + 54
Global Const WM_CAP_FILE_SET_CAPTURE_FILE As Long = WM_USER + 20
Global Const WM_CAP_FILE_ALLOCATE As Long = WM_USER + 22
Global Const WM_CAP_SET_SEQUENCE_SETUP As Long = WM_USER + 64
Global Const WM_CAP_GET_SEQUENCE_SETUP As Long = WM_USER + 65
Global Const WM_CAP_GET_VIDEOFORMAT As Long = WM_USER + 44
Global Const WM_CAP_SEQUENCE_NOFILE As Long = WM_USER + 63
Global Const WM_CAP_STOP As Long = WM_USER + 68
Private Type POINTAPI
    x As Long
    y As Long
End Type
Type CAPTUREPARMS
    dwRequestMicroSecPerFrame As Long
    fMakeUserHitOKToCapture As Long
    wPercentDropForError As Long
    fYield As Long
    dwIndexSize As Long
    wChunkGranularity As Long
    fUsingDOSMemory As Long
    wNumVideoRequested As Long
    fCaptureAudio As Long
    wNumAudioRequested As Long
    vKeyAbort As Long
    fAbortLeftMouse As Long
    fAbortRightMouse As Long
    fLimitEnabled As Long
    wTimeLimit As Long
    fMCIControl As Long
    fStepMCIDevice As Long
    dwMCIStartTime As Long
    dwMCIStopTime As Long
    fStepCaptureAt2x As Long
    wStepCaptureAverageFrames As Long
    dwAudioBufferSize As Long
    fDisableWriteCache As Long
    AVStreamMaster As Long
End Type
Type CAPSTATUS
    uiImageWidth As Long
    uiImageHeight As Long
    fLiveWindow As Long
    fOverlayWindow As Long
    fScale As Long
    ptScroll As POINTAPI
    fUsingDefaultPalette As Long
    fAudioHardware As Long
    fCapFileExists As Long
    dwCurrentVideoFrame As Long
    dwCurrentVideoFramesDropped As Long
    dwCurrentWaveSamples As Long
    dwCurrentTimeElapsedMS As Long
    hPalCurrent As Long
    fCapturingNow As Long
    dwReturn As Long
    wNumVideoAllocated As Long
    wNumAudioAllocated As Long
End Type

Sub ImportDevice()

Dim temp As Long
Dim hwand As Long
Dim hwdc As Long
Dim x As Long
Dim y As Long
Dim capst As CAPSTATUS
Dim CapParam As CAPTUREPARMS

x = GetSystemMetrics(SM_CXSCREEN)
y = GetSystemMetrics(SM_CYSCREEN)
If Tag.FLocation.Caption = "" Then
    MsgBox ("Select File Name First")
    Exit Sub
End If
Capture.Show
hwand = FindWindow("ThunderDFrame", Capture.Name)
hwdc = capCreateCaptureWindow("Capture Window", ws_child Or ws_visible, 0, 0, (x * 0.43), (y * 0.47), hwand, 0)
If (hwdc <> 0) Then
    temp = SendMessage(hwdc, wm_cap_driver_connect, 0, 0&)
    If temp = 0 Then
        MsgBox ("Failure to connect to device")
        Exit Sub
    End If
    temp = SendMessage(hwdc, wm_cap_set_preview, 1, 0&)
    temp = SendMessage(hwdc, WM_CAP_SET_PREVIEWRATE, 30, 0&)
    temp = SendMessage(hwdc, WM_CAP_FILE_SET_CAPTURE_FILE, 0&, Tag.FLocation.Caption)
    temp = SendMessage(hwdc, WM_CAP_DLG_VIDEOFORMAT, 0&, 0&)
    temp = SendMessage(hwdc, WM_CAP_GET_STATUS, Len(capst), VarPtr(capst))
    temp = SendMessage(hwdc, WM_CAP_GET_SEQUENCE_SETUP, Len(CapParam), VarPtr(CapParam))
    CapParam.fYield = 1
    CapParam.fAbortLeftMouse = 0
    temp = SendMessage(hwdc, WM_CAP_SET_SEQUENCE_SETUP, Len(CapParam), VarPtr(CapParam))
    Tag.Handle.Caption = hwdc
End If

End Sub

Sub StartCapture()

Dim temp As Long
Dim hwdc As Long

Tag.StartCapture.Visible = False
Tag.StopCapture.Visible = True

hwdc = Tag.Handle.Caption
If (hwdc <> 0) Then
temp = SendMessage(hwdc, WM_CAP_SEQUENCE, 0&, 0&)
End If

End Sub

Sub StopCapture()

Dim temp As Long
Dim hwdc As Long

Tag.StartCapture.Visible = True
Tag.StopCapture.Visible = False

hwdc = Tag.Handle.Caption
If (hwdc <> 0) Then
temp = SendMessage(hwdc, WM_CAP_STOP, 0&, 0&)
End If

End Sub

Document_XMLBeforeDelete does not catch single 'Delete' clicks

Thu, 19 Nov 2009 14:53:15 Z

I've been testing Document_XMLBeforeDelete event behaviour in Word 2007 VBA. I've come to the conclusion that this event is triggered only when one whole XmlNode (start and end tag with contents obviously) is in the DeletedRange.

Punching single Delete keystrokes delete the start and end tags fine, but without triggering the XMLBeforeDelete event. Does anyone have a workaround for this? Testing for single Delete keystrokes?

Jonathan

Using DIR from Excel VBA to list SharePoint Shared Documents

Wed, 18 Nov 2009 22:55:15 Z

Hi Everybody,

I have a number of excel files in SharePoint with data for different countries. They all have the same formatting and number of columns and are all .xlsm files.

The idea is to get all data from these separate files and to merge them into a master spreadsheet I called Summary.xls. From this summary, it will be possible to have a pivot table for all managers to have an overview of the consolidated data from all countries.

I have put together a macro on Summary.xls to search for *.xlsm on a particular folder, open each file and copy its content into Summary.xls. This works fine locally, but when I change the path from "C:\folder\name" to something like "\\hostname.com\teams\NAME\Shared Folders" it simply doesn't work. Apparently I can't use the pathway like that because the files are stored on a Web Server, so I would have to use the \Forms\AllItems.aspx somehow to retrieve that information.

Here is an example of the code I'm using:

Sub Update_Summary()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
Dim answer As String

sPath = "\\hostname.com\teams\NAME\Shared Folders"

'sPath = "C:\Users\me\Documents\Data"
'The local directory works just fine

ChDir sPath

sFil = Dir("*.xlsm")

'The issue is that here it can't find any .xlsm file, so the next line will end the loop
Do While sFil <> ""
Set oWbk = Workbooks.Open(sPath & "\" & sFil)
oWbk.Close False
sFil = Dir
Loop

End Sub

Any ideas?

Thank you all.

Changing 01/01/2009 to JAN

Wed, 18 Nov 2009 10:45:55 Z

Hi,
I am a real novice who is learning slowly.
I have cells with the date of 01/01/2009 up to 31/12/2009.
I want to add a column (I can do that bit) with just the month of the date

so 01/01/2009 would become JAN
06/02/2009 would become FEB.

Any ideas how I could do this please.

How to retrieve data from SQl/Microsift access to VBA(Excel )

Thu, 19 Nov 2009 10:07:33 Z

Hi.. i am new to VBA.. Can any one please suggest me coding for the issue.
I have tried this code: (Available in net) ..but getting automation error.

Can any one check and resolve the problem.

Sub GetDataFromSQL()


        Dim objMyConn As ADODB.Connection
        Dim objMyCmd As ADODB.Command
        Dim objMyRecordset As ADODB.Recordset

        Set objMyConn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset

         objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=XXX;Initial catalog=YYY;User ID=abc;Password=abc" ( Even i changes Integrated Security=true)

        objMyConn.Open

        Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = "select * from Emp1"
        objMyCmd.CommandType = adCmdText

        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open


        ActiveSheet.Range("A1").CopyFromRecordset objMyRecordset

End Sub

Pass variables between subroutines

Fri, 20 Nov 2009 20:04:22 Z

I would like to combine several into one variable "parameters" to pass between subroutines.

sub ThisSub()
dim parameters as _______
...
parameters.a1=a1
parameters.a2=a2
parameters.a3=a3
...
parameters.a10=a10

ThatSub(parameters)
end sub

Sub ThatSub(parameters as ______)
a1=parameters.a1
a2=parameters.a2
a3=parameters.a3
...
a10=parameters.a10
end sub

In vb.net you use a Public Class to define "parameters". How do you do it in VBA?

Multiple Pivot Table Filter Update

Fri, 20 Nov 2009 19:22:14 Z

I created some code that changes the filter selected on PivotTable1 to the other 9 Pivots on the same worksheet, but I had to create a button to trigger the macro. I've done VBA in Access and know that there is a AfterUpdate trigger, does excel have this to? I want to run the macro after the filter selection has been made in the pivot table. Thanks MAS

VBA editor does not seem to reset

Fri, 20 Nov 2009 22:44:52 Z

I am running excel 2003

while trouble shooting my code, I hit run->reset and then debug->step into
to begin another round.

However, I don't seem to get a consistent reset as indicative of the fact the
editor does not step into my code when I command it to do so. (no yellow highlight and no movement of the cursor)

What am I doing wrong so I can consistently get my code to reset and not have to fumble around until
something magically happens so that I can proceed.

My hardware is sound. I think I am not cleanly ending my code in some manner and that is my problem.

Bil

how to save pictures from excel in another folder

Sun, 22 Nov 2009 19:11:18 Z

Hi

I have several excel files with pictures in them.
I would like to save the pictures so i can use them on my website.they were sebt to me for this purpose.
When i right click on a picture the SAVE AS is not shown.

any clues of how to do it,

Thanks

Formula does not calculate

Sun, 22 Nov 2009 22:56:46 Z

I have a piece of code that does the following:

Grabs a cell.value from another worksheet places it in the new worksheet A1
Formula in B1 reads A1 and should update resulting in a string

Unfortunately this Formula does not update unless i exit the macro. The formula is:

Developed by a third party
I have tried to do this Range("B1").calculate
Put timer loops to give the formula time to pull the data it needs

None of this works unless I exit the macro, the minute I do the Formula gives the desired result.

Thank you in advance

how to get visible items count of a pivot field (pivot table report on table/view)

Tue, 24 Nov 2009 05:54:28 Z

I would like to get visible items count of a pivot field (pivot table report on table/view)

if my PivotReport is connected to cube i can get it from PivotFields.VisibleItemsList.Count

This is not working in pivot table report from a SQL table/view

Thanks Dileep

Refresh Excel Pivot Table using data from SQL Server

Tue, 24 Nov 2009 03:08:42 Z

Hi,

Can anyone help me refresh an excel pivot table, on a given event (sheet_Activate or similar), by calling a reselect on a view in SQL server.

I can't find code anywhere on the internet to do it.

Thanks and warm regards,

CheckSpelling in Word containing protected fields

Tue, 24 Nov 2009 02:27:14 Z

Is anyone able to guide me on the following post from the Kiwi Koder:

While the article at http://word.mvps.org/faqs/macrosvba/SpellcheckProtectDoc.htm is great if you

want to check the spelling of the information input into formfields, my problem is a bit different. 

I have (Word 2003) templates that produce forms-protected
 documents, but the documents themselves 

don't contain any formfields. I use forms protection only because it allows me to protect only

certain sections of the document, such as the header and footer, while leaving other sections, 

such as the main body of the document unprotected.


The spellchecker checks the spelling in
 the unprotected sections OK, and I don't really mind if it 

doesn't check the spelling in
 the protected
 sections because 1) I know that I haven't made any spelling 

errors in
 the 'boilerplate' in
 these sections; and 2) if the user has made a spelling error in

the 'variable' information, they can just rerun the code in
 template to correct the errors. However, 

there is a deficiency in
 the native spellchecker that I've been asked to develop a workaround for.


In
 an unprotected document, if none of the choices in
 the 'Suggestions' list are viable, you can just 

type in
 the 'Not
 in
 Dictionary
 ' box to make the change. However, this doesn't work in
 a protected

document; the text in
 the 'Not
 in
 Dictionary
' box is treated like it's in
 a protected
 section. I could

always write code that unprotects the document before displaying the spellchecker, but there are a couple 

of drawbacks:


First, this will allow the users to spellcheck the protected
 sections as well, which is not
 really a major 

problem except that if the user corrects an error that was introduced through the input of the 'variable' 

information described above, this correction won't get replicated back into the data from the UserForm. 

Thus if the user reruns the template, the correction won't be there and the error could be reintroduced 

without the user realising it.  However, I can live with this.


The real problem is that the spellchecker dialog isn't modal to Word.  Therefore, if I unprotect the 

document, the user now has full access to the whole document, _including_ the protected
 sections. And 

believe me, the users will take advantage of this whenever possible; I learned this when I told the 
users I was going to fix a bug in
 my code that left the document unprotected and got a... umm... less 

than favourable response. ;-D


Does anyone know a way to enable the editing capability in
 the 'Not
 in
 Dictionary
' box without 

unprotecting the document? Or will my users just have to live with this limitation? (The second choice 

is acceptable as I'm quite happy to tell the users 'No' - as long as I can justify the response.)

Thanks!

Can date created and date modified etc be set from code?

Mon, 23 Nov 2009 20:31:34 Z

Please can anyone help. I need to be able to set the date last modified, and ideally the date created (but I can work round this by copying the file) of files in the operating system filing system (not tables in Access) I cannot seem to find any way of doing this from VBA in Access 2007. Is it possible and if so how? Thanks. Steve

How to call VBA userform from Excel Button (or) How to call VBA userform from workbook

Mon, 23 Nov 2009 10:20:05 Z

Hi ,

I have taken an autoshape in Excel ( for button).
In VBA i ahve one form.

So, my issue is .. how can we access VBA form through the button in excel

or how can we access VBA form through Thisworkbook in VBA project

Please help me in this issue

read properties

Sun, 22 Nov 2009 08:49:31 Z

Using Visual Studio (VB) I need to get the author and category properties from a closed access.adp (2000 version) file, as a means of checking the version no (which I've entered in the category property), so that if a user's application in their C: drive has been superceded I can copy an update version from the network. I tried the filesystemobject but it only gives geneal properties such as file size. I'm developing on Vista for target XP clients on a windows server 2003 system. The network copy of the application is classed as the 'live' master and I'm after updating their local copy if it is out of date. Users will run this pre loader and if the version is OK will launch the application, otherwise it will copy the latest version and then launch it