[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

VBA

10K

Solutions

3K

Contributors

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Share tech news, updates, or what's on your mind.

Sign up to Post

VBA to Export Excel sheet to PowerPoint:

Hi experts,

I am using the following VBA to export my worksheet to PowerPoint presentation, however if I change the slicer in my Pivot Chart and export again the worksheet, the code is creating a new presentation, rather than a new slide.

I would like to add the new selection into the same presentation, just adding as a new slide.

I believe I need a loop indexing and counting the slides.

Any suggestions, please.


VBA Code:

Sub ExceltoPowerPoint()

Dim PowerPointApp As Object
Dim myPresentation As Object
Dim wb As Workbook
Dim ws As Worksheet
Dim namecheck As Range

Set PowerPointApp = GetPowerPointApp()
Set myPresentation = PowerPointApp.Presentations.Add

Call ExportResourcePlanSlide(myPresentation, ThisWorkbook.ActiveSheet.Range("a2:m40"))

PowerPointApp.Visible = True
PowerPointApp.Activate

Application.CutCopyMode = False

End Sub



Function GetPowerPointApp() As Object

On Error Resume Next
Set PowerPointApp = GetObject(class:="PowerPoint.Application")
Err.Clear
If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")
If Err.Number = 429 Then
MsgBox "PowerPoint could not be found, aborting."
Exit Function
End If
Set GetPowerPointApp = PowerPointApp
On Error GoTo 0

End Function


Sub ExportResourcePlanSlide(ByVal myPresentation As Object, ByRef rng As Range)

'Create new …
0
Introduction to R
LVL 12
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Using MS Access 2016 I am writing a VBA script to loop through an application window to retrieve the text/values. I was hoping someone could help me understand why a text box would have two classnames and id's (hwnd). Using AutoIt the classname that is the same as the main window also shows the name of the control (textbox). The other classname for the textbox has the word Edit in it but the control name is blank.

I can get the text that is in the textbox but I also need to get the control name so I can identify what its for.

Hope this makes sense.
0
I am using Access 2003 (yes I am no jokes).... I have a Combobox to look up information... (date of birth). Users types in the date and bingo we find a match and goes to the correct record.

The issue I'm having is that when a user starts to type and then deletes the initial entry Access still wants to look up the data in the table.  Currently I have the combo box set with a row source of: SELECT tMain.DOB, tMain.LName & ", " & tMain.FName, tMain.TrayNo FROM tMain ORDER BY tMain.DOB DESC

I think I need to somehow move this code out of row course and fire it after an update.  Does this sound right and if so how I can use VBA to execute the search when there are at least 8 characters in the combo box to start the search?  I was thinking in the event "after update" to check the len and if over 8 set the row source in the combo box to SELECT tMain.DOB, tMain.LName & ", " & tMain.FName, tMain.TrayNo FROM tMain ORDER BY tMain.DOB DES??? Doesn't seem like the best approach.

Thank you.
0
Is there any way to pass query parameter through vba in ms Access. Any attached example will be highly appreciable.
0
Hi Experts,

I have attached a VB Utility being regularly used by me. It is used to prepare some returns, where we input the data and the output is produced in the shape of some encrypted file format for onward submission. But it can also be used to prepare a correction return also. In that case we need to import a Consolidated file and then the return can be amended or extended.

What I presently do
--Download all the conso files

--Import the files one by one in above software.
>>Here we need to Select Form No (24Q / 26Q / 27Q / 27EQ).
>>Type of statement to be prepared is always "Correction".
>>Option to be selected "Import Consolidated File"

--Copy the data from the tabs and paste them in different sheets of Excel (Normally there are 3 Tabs, but data from 1st Tab is never copy pasted. There may be 4 tabs for the 24Q - 4th Quarter of a Financial year in which case the last 3 tabs are copy pasted)

Name of Tabs (from which data to be copied and pasted in Excel) in case of each Form
24Q >  Challan , Annexure I        (in case of 4th Quarter of FY tabs to be Copy pasted are Challan, Annexure I and Annexure II
26Q >  Challan , Annexure I
27Q >  Challan , Annexure I
2EEQ >Challan , Annexure I

Challan Data can be pasted in the same Excel Sheet for All Form Types, whereas for Annexure I and Annexure II, we need separate sheets for Each Form.

I wish some script (or anything like that even if that is in Excel) which can
0
Hello everybody.

I have a folder that contains many workbooks.

I need, for any workbook:
1) check if is a .xlsm workbook;
2) if yes, check if a sheet named "Record" exists;
3) if exists, import stuff from sheet "Record" to thisworkbook.

How can I identify the required workbooks (points 1 and 2)?

Thank's.
0
Using Microsoft Access (2003)
I have a form with a text box called PicFileName and an Image Control called PersonImage
The intent is that the following code will cause .jpg (picture) to appear in PersonImage, without the .jpg being embedded in the table behind this form (in order to save space)
The following code accomplishes this perfectly:

Dim strPicName As String
Dim strPicPath As String
Dim strSource As String

strPicPath = C:\Pix                 C:\Pix is the folder containing the jpg
strPicName = ABCD.jpg       ABCD.jpg is the filename of the picture

strSource = "" & strPicPath & strPicName & ""              in this case it will be C:\Pix\ABCD.jpg

If Not IsNull(Me.PicFileName) And Me.PicFileName <> "" Then
    Me.PersonImage.Picture = strSource
End If


As I said this works perfectly in all computers on my LAN, EXCEPT for ONE which returns an error 2114 saying that this code is not supported OR the jpg is too large!  I have reduced the size of the .jpg to a few K, to no avail.

ALL COMPUTERS ON THE LAN USE THE SAME OS (Win 10 Pro), AND THE SAME VERSION OF ACCESS, AND GET THEIR JPG FROM THE SAME FOLDER,  and the above process runs perfectly for the last six years!!

QUESTION: What needs to be adjusted in the one computer where this is not working?
0
I need a technique or code for Access 2002 where I supply a table name and the results list field names, data type (optional) and row count for each field.
0
I'm running outlook 2016 with exchange server on premise 2016.

I use to recive many empty mail with only .eml attached. The .eml file is the email I have to read, replay, forward ecc...

Actually I manualy drag and drop the mail to a subfolder. Is there a way to do it automatically?
0
Using Microsoft Access 2016 (wish I had another choice)  I am trying to write a script that will loop through an application window and get the values for each textbox, combobox, listbox, checkbox....

I found a script (currently for use on Excel) that goes through all windows but will not return the value/text for any "Edit" control. I intentionally left a "Save As" window open. It can see the Filename combobox and that it is an "Edit" control but didnt return the value. The application I am trying to read the data isnt popular so I figure this approach would be easier than trying to explain the application.

I have tried various sendmessage, FindWindowEx, SendMessageGETTEXT, GetWindowText configurations and am missing something (besides a few brain cells). In short, I could really use some help changing the script to pull the text/value from any control regardless its type. Once this is working I will then change it to work on Access to update a table.


After I am able to read all the controls I plan on being able to update fields and save the changes in the application if that makes a difference in your solution.


First time using this site (I'm sure by the question that's obvious).

Thank you in advance for your time and assistance.

Option Explicit
 
Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare 

Open in new window

0
Build an E-Commerce Site with Angular 5
LVL 12
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

I'm getting a Run-time error 438 - object doesn't support this property or method.
this only happens like every third or fourth time I run the code
Prior to executing, I'm sure all word instances are shutdown
Environment:  Access 2016 - running VBA to manipulate MS Word 2016 document.
All the references are good I believe (included Word object module, etc)

see attached word file and simple database - see module 1 for sample
it generates a 438 on the line
owordapp.activedocument.content.selection.Goto what:=wdgotobookmark, Name:="photo"
Option Explicit
Sub photoproblem()

Dim owordapp As Object, i As Integer, a As String, v As Shape, rng As range, dir1 As String
Dim template As String, newphoto As String

dir1 = "U:\Data\photoproblem\"
template = dir1 & "SAMPlE FILE.docx"
newphoto = dir1 & "dolphin.png"


Set owordapp = CreateObject("Word.Application")

With owordapp

    .Documents.Open template
    .Visible = True
    .screenupdating = True
    
End With

owordapp.activedocument.content.selection.Goto what:=wdgotobookmark, Name:="photo"

Set rng = selection.range

Set v = activedocument.inlineshapes.addpicture(FileName:=newphoto, range:=rng, savewithdocument:=True).converttoshape

Set rng = Nothing
Set owordapp = Nothing

End Sub

Open in new window

Sample-file.docx
photoproblem1.accdb
dolphin.png
0
Dear All,

I have a few questions regarding Microsoft Access reports (I am using 2016, but the same issues exist in 2013).

i) let's say I have created a report using the report wizard and when prompted I selected stepped for its layout. I have created and saved it, but my boss doesn't like it and he wants to use outline instead; I could not find a way of changing the layout, it seems to be only possible only through the Report Wizard or do I need VBA (or is it just not possible)?
ii) I have created a report with groups and I want the report header to be with the first group and the report footer on the same page after the last group. I am using Force New Page equals none for the group header and after each session for the group footer. This does the trick for the header but not for the footer. is there any way this can be achieved?
iii) (form question) when someone selects Justified as the form layout, would anyone be able to tell me where is the default value for the form's width (there doesn't seem to be anything like Normal.dot for Access, would that be in the Registry)? Also, if you have Justified layout and you change the width of the textfields, it looks like Access is not picking up automatically the new dimensions, but you would have to close and redo the form - any idea why this is or am I doing something horribly wrong?

Regards,
George
0
I have a macro that will generate an email. Is there a way to put one of the lines in Bold? Here is the body of the email. I want to put the name "John" in bold.

strbody = "Hi Robbie," & vbNewLine & vbNewLine & "ConfigsOnly.xlsm has been updated." _
    & vbNewLine & vbNewLine & "Thanks!" & vbNewLine & "John"

Open in new window

Thanks,
John
0
Now the dates look like this: 2009-11-04. And the cells are formatted as date. I want to change to custom format to dispaly 20091104. Can I just do that in Excel 2007, go to custom format and enter "YYYYMMDD" and the contents will still count as date?
0
0
I have a VBA macro which, on Excel 2000 has worked for several users for several years but since upgrading to Windows10 and Excel 2016 , one specific instruction doesn't work


My code is

Workbooks(DataName).activate

….

The value of the value "DataName" refers to a valid, open workbook (The other currently open workbook , from which I am trying to move active status, is "ProgName". After executing the instruction, the variable ActiveWorkBook.name remains as ProgName and I would expect it to be DataName
0
I have an Access application that can export fields to a PDF file.  However, for this work it needs the full version of Adobe installed.  Not just the reader.  So I was wondering if there was a way to check programmatically to see if Adobe Acrobat was loaded and not just the reader.

Thanks John
0
Hi Bill,

You amended some code for me a few weeks ago which worked perfect.. see code below

Option Explicit

' Define files and folders to work with (NO TRAILING BACKSLASH)
Const cBaseDir = "b:\ee\ee29122035\files\[[DATE]]"
Const cControlFile = "b:\ee\ee29122035\list.csv"
Const cDelim = ","

' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

' Declare global variables
Dim objFSO, strBaseDir, strControlFile, strDate
Dim arrControl, strControl, arrFields, i, arrTemp
Dim strOldName, strNewName, strOldPath, strNewPath

' Create file system object
Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")

' Get date in YYYYMMDD format
strDate = Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2)

' Resolve file paths
strBaseDir = objFSO.GetAbsolutePathname(Replace(cBaseDir, "[[DATE]]", strDate, 1, -1, vbTextCompare))
strControlFile = objFSO.GetAbsolutePathname(cControlFile)

' Make sure base folder exists
If Not objFSO.FolderExists(strBaseDir) Then
    Wscript.Echo Now & " ERROR: Base folder does not exist - " & strBaseDir
    Wscript.Quit
End If

' Make sure control file exists
If Not objFSO.FileExists(strControlFile) Then
    Wscript.Echo Now & " ERROR: List file does not exist - " & strControlFile
    Wscript.Quit
End If

' Load csv file into array
With objFSO.OpenTextFile(strControlFile, ForReading)
    arrControl = Split(.ReadAll, vbCrLf)
    .Close
End With

' Loop over each control record and process 

Open in new window

0
I have attachment files, of various types, which have been converted to binary format and stored within my SQL Server database table.

I need to convert the binary file data to Base 64. Does anyone have a coding module written in VBA which can accomplish this?
0
OWASP: Forgery and Phishing
LVL 12
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

I am trying to manipulate an Excel file through VBA, but in an Access 2016 database, and I cannot get a pivot table to sort after trying many variations of the AutoSort statement.

The file is created by this command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, parmQDefName, parmOutputFileName, True

Open in new window


Once the file is reopened I rename and format the first tab, then select and name the data as a named range.
Next, I append another tab after that for the pivot table, create a pivot table, create a pivot cache, display it as xlTabularRow, turn off grand totals and freeze the headings.
After adding my rows and columns it is time to create a pivot value.  

In my topmost function I defined a new instance of Excel (is that the right term?) to work with the file within Access VBA:
Dim excel As excel.Application

Open in new window


If I Dim workbooks, worksheets, ranges, pivot tables etc. as child objects of my Excel.Application, everything seems to work correctly in my called functions if I use ByRefs.

QUESTION:
Why can I reformat the "Invoice Amount" field, but not sort on it?

This works:
Dim pt As PivotTable
Dim pf As PivotField
    Set pf = pt.PivotFields("Sum Of Invoice Amount")
    pf.NumberFormat = parmNumberFormat

Open in new window


But i see no change after running either of these two fully-qualified statements and refreshing the table:
    excel.Workbooks(1).Worksheets(2).PivotTables(1).PivotFields(4).AutoSort xlAscending, "Invoice Amount"
    pt.RefreshTable

    excel.Workbooks(1).Worksheets(2).PivotTables(1).PivotFields(4).AutoSort xlAscending, "Sum of Invoice Amount"
    pt.RefreshTable

Open in new window


I even recorded a macro in Excel to confirm that my syntax is correct (it is).
0
I have a file which has VBA code written using a 32 bit Excel 2010.   It has been working fine for a client who is using a 64 bit Excel 2010.   It has recently started crashing on the clients computer and we cannot determine why.  In addition, it has just started to increase in size whenever some of the macros are run.   This increase in size is also happening to a file it is sharing data with.  In checking, this data is not visible and when Ctr end is used the cursor ends up significantly below and to the right of the actual data in both files.

I am not sure if the two problems are related and would appreciate suggestions on how to eliminate them both.
0
I'm having trouble executing a SQL update query in MS Excel. I want to update names from a Corrections tab to several other tabs, but no matter what I try I get the error #/message:

-2147467259: Operation must use an updateable query.

I know the setup is correct because it works to put the Corrections and other data into recordsets. Here's a simplified version that would update data on just a single tab:

     Public Sub NameCorrex()

     On Error GoTo Handle_Error

     Dim cn As Object
     Dim rs As Object

     ' create ADODB connection to this workbook
     ' --------------------------------------------------------------------
     Set cn = CreateObject("ADODB.Connection")
     cn.Open _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source='" & ThisWorkbook.FullName & "';" & _
      "Extended Properties=""Excel 8.0;HDR=Yes;FMT=Delimited;IMEX=1;"";"

     Set rs = cn.Execute("UPDATE [Corrections$] INNER JOIN [Open Claims$] " & _
     "ON [Corrections$].[Change From] = [Open Claims$].[Processor (Full Name)] " & _
     "Set [Open Claims$].[Processor (Full Name)] = [Change To] " & _
      "WHERE ((([Open Claims$].[Processor (Full Name)])=[Change From]));")

     rs.Close ' Close the recordset
     Set rs = Nothing ' Clean up

     Done:
     Exit Sub
     Handle_Error:
     MsgBox (Err.Number & ": " & Err.Description)

     End Sub

Thanks in advance to anyone who can help.
0
msaccess, powerpoint, vba

I am using vba in msaccess to change link references in powerpoint. The script I found has

Dim oSld As Slide
Dim oSh As Shape

I am getting an error 429, ActiveX Component can't create object.

I think I do not have the proper object library loaded in msaccess 2016.

Any suggestions.

Thanks,
WillieCg
0
Is there an addin besides MZTools to makes some  vba code upper case
0
Hello everybody.

I have to download a file by the navigation of a hyperlink.

The hyperlink address is in cell B1 of sheet 2: something like

https://rrr.xxxxxxx.com/example/?Example=2580,^xxxx,^2299^xxxx^xxxxxxxxxxxxxxxxxxxx==

The file name is simply file.xls

Neither user nor password are required.

How could I automatically save it in a specific folder?
0

VBA

10K

Solutions

3K

Contributors

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.