WordPress - Excel functions (AI)

3 functions to download posts, adds new post and to upload and set featured image using MySQL DB for WordPress post.
These were created by Bing AI, so they might be rough, Not Tested yet.
AddPostToWP            Adds Post to WP website with no featured image (MySQL DB)
UploadPictureToWP        Uploads image to WP site and sets it as certain post featured image (MySQL DB)
DownloadPosts            Downloads Posts as table into spreadsheet from WP site (MySQL DB)

CodeFunctionName
What is this?

Public

Not Tested

Imported
Sub DownloadPosts()
    ' I can try to generate a macro for you, but I cannot guarantee that it will work as expected.
    ' You will need to have a MySQL ODBC driver installed and a DSN name configured for your database connection.
    ' You will also need to enable the Developer tab and the macro settings in Excel. Here is a possible macro that you can use or modify:
    ' Source: Conversation with Bing, 4/18/2023
    ' (1) How can VBA connect to MySQL database in Excel?. https://stackoverflow.com/questions/13558921/how-can-vba-connect-to-mysql-database-in-excel.
    ' (2) Quick start: Create a macro - Microsoft Support. https://support.microsoft.com/en-us/office/quick-start-create-a-macro-741130ca-080d-49f5-9471-1e5fb3d581a8.
    ' (3) Connecting to MySQL through Excel by running a Macro. https://stackoverflow.com/questions/5349947/connecting-to-mysql-through-excel-by-running-a-macro.
    ' (4) How to import an excel file in to a MySQL database. https://stackoverflow.com/questions/1310166/how-to-import-an-excel-file-in-to-a-mysql-database.
    ' (5) Querying MySql DB with Excel VBA - LinkedIn. https://www.linkedin.com/pulse/querying-mysql-db-excel-vba-sachdeep-sivakumar.
    ' Declare variables
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sql As String
    Dim ws As Worksheet
    Dim i As Long
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Set worksheet
    ws.Range("A2:G1000").ClearContents ' Clear previous data
    Set conn = New ADODB.Connection ' Create connection object
    conn.Open "DSN=YourDSNName" ' Open connection with DSN name
    Set rs = New ADODB.Recordset ' Create recordset object
    'Define SQL query to get posts with categories, tags and featured images
    sql = "SELECT p.ID, p.post_title, p.post_date, t.name AS category, GROUP_CONCAT(t2.name) AS tags, m.meta_value AS featured_image " & _
        "FROM wp_posts p " & _
        "LEFT JOIN wp_term_relationships r ON p.ID = r.object_id " & _
        "LEFT JOIN wp_term_taxonomy x ON r.term_taxonomy_id = x.term_taxonomy_id " & _
        "LEFT JOIN wp_terms t ON x.term_id = t.term_id AND x.taxonomy = 'category' " & _
        "LEFT JOIN wp_terms t2 ON x.term_id = t2.term_id AND x.taxonomy = 'post_tag' " & _
        "LEFT JOIN wp_postmeta m ON p.ID = m.post_id AND m.meta_key = '_thumbnail_id' " & _
        "WHERE p.post_type = 'post' AND p.post_status = 'publish' " & _
        "GROUP BY p.ID"
    rs.Open sql, conn ' Open recordset with SQL query
    If Not rs.EOF Then ' Check if recordset is empty
        For i = 0 To rs.Fields.Count - 1 ' Copy field names to worksheet
            ws.Cells(1, i + 1).Value = rs.Fields(i).Name
        Next i
        ws.Range("A2").CopyFromRecordset rs ' Copy data to worksheet
        ws.Columns.AutoFit ' Format worksheet
        MsgBox "Download completed.", vbInformation ' Display message
    Else
        MsgBox "No records found.", vbExclamation ' Display message
    End If
    rs.Close ' Close recordset and connection
    conn.Close
    Set rs = Nothing ' Clean up objects
    Set conn = Nothing
End Sub
Sub UploadPictureToWP ()
    ' Okay, I can try to write a macro for you.
    ' One way to upload a picture to a WordPress site and set it as a featured image for a post is to use the wp_insert_attachment and set_post_thumbnail functions and connect to the database using ADODB. Here is an example of such a macro:
    ' You need to modify the connection details, the range and the post details according to your needs.
    ' You also need to make sure that the WordPress database and functions are accessible from your Excel macro.
    ' You can find more details and examples on how to upload a picture to WordPress and set it as a featured image in these links.
    ' Source: Conversation with Bing, 4/18/2023
    ' (1) wp insert post - How do I set a featured image (thumbnail) by image URL .... https://wordpress.stackexchange.com/questions/40301/how-do-i-set-a-featured-image-thumbnail-by-image-url-when-using-wp-insert-post.
    ' (2) Insert woocommerce products programmatically with featured image and .... https://wordpress.stackexchange.com/questions/402920/insert-woocommerce-products-programmatically-with-featured-image-and-gallery.
    ' (3) How to Allow Users to Upload Images on a WordPress Site - WPBeginner. https://www.wpbeginner.com/plugins/how-to-allow-users-to-upload-images-on-a-wordpress-site/.
    ' (4) How to Insert a Picture in Microsoft Excel - How-To Geek. https://www.howtogeek.com/764451/how-to-insert-a-picture-in-microsoft-excel/.
    ' (5) Excel VBA and the ImageBox Control - Home and Learn. https://www.homeandlearn.org/load_an_image_into_an_imagebox.html.
    Dim DBconnection As Object
    Dim post_id As Long
    Dim image_url As String
    Dim image_data As Variant
    Dim filename As String
    Dim upload_dir As String
    Dim file As String
    Dim wp_filetype As Variant
    Dim attachment As Object
    Dim attach_id As Long
    Dim attach_data As Variant
    post_id = Range ("A1") 'The cell where the post ID is ' Set the post ID and the image URL from Excel sheet
    image_url = Range ("B1") ' The cell where the image URL is
    image_data = Application.WorksheetFunction.EncodeURL (image_url) ' Get the image data and filename from the URL
    filename = Application.WorksheetFunction.EncodeURL (Application.WorksheetFunction.Right (image_url, Application.WorksheetFunction.Find ("/", Application.WorksheetFunction.Substitute (image_url, "/", "", Application.WorksheetFunction.Len (image_url) - Application.WorksheetFunction.Len (Application.WorksheetFunction.Substitute (image_url, "/", ""))))))
    Set DBconnection = CreateObject ("ADODB.Connection") ' Connect to the WordPress database
    DBconnection.Open "Provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog=YourDatabaseName;User ID=YourUserID;Password=YourPassword;" ' Enter your connection details here
    upload_dir = DBconnection.Execute ("SELECT option_value FROM wp_options WHERE option_name = 'upload_path'") ' Get the upload directory from the database
    If Right (upload_dir, 1) = "/" Then ' Create the file path and save the image data to the file
        file = upload_dir & filename
    Else
        file = upload_dir & "/" & filename
    End If
    Open file For Binary Access Write As #1
        Put #1, , image_data
    Close #1
    wp_filetype = Application.WorksheetFunction.EncodeURL (Application.WorksheetFunction.Right (filename, Application.WorksheetFunction.Find (".", Application.WorksheetFunction.Substitute (filename, ".", "", Application.WorksheetFunction.Len (filename) - Application.WorksheetFunction.Len (Application.WorksheetFunction.Substitute (filename, ".", "")))))) ' Get the file type from the filename extension
    Set attachment = CreateObject ("Scripting.Dictionary") ' Create an attachment object with the file details
    attachment.Add "post_mime_type", wp_filetype
    attachment.Add "post_title", filename
    attachment.Add "post_content", ""
    attachment.Add "post_status", "inherit"
    attach_id = DBconnection.Execute ("CALL wp_insert_attachment (" & attachment & ", '" & file & "', " & post_id & ")") ' Insert the attachment into the database and get its ID
    attach_data = DBconnection.Execute ("CALL wp_generate_attachment_metadata (" & attach_id & ", '" & file & "')") ' Generate the attachment metadata and update it in the database
    DBconnection.Execute ("CALL wp_update_attachment_metadata (" & attach_id & ", " & attach_data & ")")
    DBconnection.Execute ("CALL set_post_thumbnail (" & post_id & ", " & attach_id & ")") ' Set the attachment as the featured image for the post
    DBconnection.Close ' Close the connection
    Set DBconnection = Nothing
End Sub
Sub AddPostToWP ()
    ' Sure, I can help you with that. One way to write an Excel macro to add a post to a WordPress site using MySQL database is to use the wp_insert_post function and connect to the database using ADODB. Here is an example of such a macro:
    ' You need to modify the connection details, the range and the post details according to your needs.
    ' You also need to make sure that the WordPress database and functions are accessible from your Excel macro.
    ' You can find more details and examples on how to add a post to WordPress using MySQL in these links.
    ' Source: Conversation with Bing, 4/18/2023
    ' (1) wp_insert_post() | Function | WordPress Developer Resources. https://developer.wordpress.org/reference/functions/wp_insert_post/.
    ' (2) Inserting a post in Wordpress using MySql - Stack Overflow. https://stackoverflow.com/questions/1670838/inserting-a-post-in-wordpress-using-mysql.
    ' (3) Import Post, Pages, Products Content in WordPress & WooCommerce with Excel. https://wordpress.org/plugins/content-excel-importer/.
    ' (4) how to insert an article into a wordpress database using mysql. http://www.thinkoholic.com/2018/05/30/how-to-insert-article-wordpress-database-mysql/.
    Dim DBconnection As Object
    Dim my_post As Object
    Dim post_title As String
    Dim post_content As String
    Dim post_status As String
    Dim post_author As Long
    Dim post_category As Variant
    'Set the post details from Excel sheet
    post_title = Range ("A1") ' The cell where the post title is
    post_content = Range ("B1") ' The cell where the post content is
    post_status = "publish" ' The status of the post (draft, publish, etc.)
    post_author = 1 ' The ID of the post author
    post_category = Array (8,39) ' The IDs of the post categories
    Set my_post = CreateObject ("Scripting.Dictionary") ' Create a new post object
    my_post.Add "post_title", post_title
    my_post.Add "post_content", post_content
    my_post.Add "post_status", post_status
    my_post.Add "post_author", post_author
    my_post.Add "post_category", post_category
    Set DBconnection = CreateObject ("ADODB.Connection") ' Connect to the WordPress database
    DBconnection.Open "Provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog=YourDatabaseName;User ID=YourUserID;Password=YourPassword;" ' Enter your connection details here
    DBconnection.Execute "CALL wp_insert_post (" & my_post & ")" ' Call the wp_insert_post function with the post object as argument
    DBconnection.Close ' Close the connection
    Set DBconnection = Nothing
End Sub

Views 454

Downloads 67

CodeID
DB ID