I had this question after viewing MS Access Using VBA Split Word Docx file into separate Doc for Each Page.

Everything is working as "advertised" but the code is inserting an additional (blank) page for each of the pages in the original document except for the last page in the merge document. I have tried to figure this out for over a day but I am bumping up against my limited knowledge. Could someone please help?

I receive survey data from multiple third party vendors.  The data comes to me in a crosstab table format in excel.  I need a way to make the table into regular format, so I can define relationships to other tables.   I need a vba procedure/function that can change the format automatically.

Example format - Question are S1, S2, S3, S4A
RespondentID      S1      S2      S3       S4A      S4B
1      1      1      2      1      
2      1      6      1      2      1
3      2      3      4      2      1
4      2      6      2      2      3
5      1      4      5      2      2

The format I needed
QuestionID      ResponseID      Value
S1                               1                 1
S2                               1                 1
S3                               1                 2
S4A                               1                 4A

I will include the access database I am working with.  The table I am trying to transform is called - M_RespondentAnswers. This needs to relate to other tables in relationship window. I trying to create something like a star schema in access, with survey being the fact table and the other tables like a dimensions for reporting purposes.
MIKE_SURVEYS_2017.accdb
individual_columnlisting.xlsx

access 2010
form
subform

I have a form called "main"
subform called "dbo_t_redbook_pricing_escalation_detail_subform"

I need to loop through 2 columns
on the subform to see if my conditional formatting has changed the fore color/background color to red ?


if it does i need a msgbox or boolean alert.
I will  be calling the alert from a Command button on my main form.



Thanks
fordraiders

I have several hundred fillable PDFs that I am trying to complete programatically in an Access database.

I have cobbled together something that will enter the required data (this is vastly cut down to highlight the issue)

Public Sub FillForm (strPhone, strAddress, strUserName, strEmail as String)
Dim acroApp As acroApp
Dim PDDoc As AcroPDDoc
Set acroApp = New acroApp
Set PDDoc = New AcroPDDoc
Set avdoc = CreateObject("AcroExch.AVDoc")
filenm = "L:\Sandbox.pdf"
If avdoc.Open(filenm, "") Then
    Set PDDoc = avdoc.GetPDDoc()
    Set pdfform = CreateObject("AFormAut.App")
    pdfform.Fields("Daytime telephone").Value = strPhone
    pdfform.Fields("Email address").Value =strEmail
    pdfform.Fields("Correspondence").Value = strAddress
    pdfform.Fields("undefined_148").Value = strUserName
    PDDoc.Save 1, filenm
End If
End Sub

This worked fine on the pdf I was using to test but it seems that although all the forms look the same the number of undefined fields differs so what would be "undefined_148" in one is "undefined_207" in another or "undefined_122" in another etc.
It is always the next field after "Correspondence" though so what I need is some way of moving one field on from the "correspondence"  or finding the tab index of "Correspondence" and then referring to whatever the next field is called by its tab index.

Any help would be greatly appreciated.

Thank you
Richard

Okay, so I'm trying to use the runas with smart card credentials.  It works, but only through the command line window.  Instead I would like it to automatically bring up the standard GUI that shows the smart cards.

Example:  I want to use getmac to grab a mac address of a computer on the network.  I can only do this with credentials.  We use smart cards only.  I'm trying to get that info, redirect it to a simple text file, and then display that text file later.  Yes, I can get it to work in the command line window, but I want to select the smart card from the GUI instead.  Any way to do this??

VBA Code:

Shell "runas /smartcard getmac /s " & ipaddress & " >>MACINFO.TXT"
Shell "notepad MACINFO.TXT"

Dear Experts:

I would like to change line 3 of this code snippet to take the following circumstance into account:

The user-defined building block entry has now been saved to 'Building Block.dotx' instead of the Normal Template.

How would I have to change line 3 to accomodate for this new requirement?

Set rng = ActiveDocument.Paragraphs(2).Range
rng.Font.Size = 18
    NormalTemplate.BuildingBlockEntries("My_TOC_Just_Set_Names").Insert Where:= _
        Selection.Range, RichText:=True

Select all Open in new window

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

For control purpose I was thinking that, if  a sales tax invoice is approved in the application then it should never be reprinted again the same way we do not allow the approved sales invoice not to be deleted. I know we can still delete unapproved invoice but once approved it is locked , now I want to do the same with reprinting:

(1) I'm thinking of designing a VBA code like below:

Dim Cancel as Interger
IF " approved" = Dlookup(" Status", " tblCustomerInvoices"," [InvoiceID] = " &Me.CboInvoiceID) Then
MsgBox " Please note that approved invoices cannot be reprinted"
Cancel = True
Exit Sub
End If

Can the above code stop the printing  or are there better ways of doing it , any suggestion is very welcome

I have not yet tried the code, I can clearly state here that the code is my own thinking in term of financial internal controls

By the way assuming it is okay which event should I place it on a form used to print the invoices

Regards

Chris

I have this VBA that works great but I want to add a specific sheet name to it.  The sheet name is MAIN_PN.  What would the code be with sheet MAIN_PN added to it?

Sub Trim()
Dim c As Range
For Each c In Range("A1:A8225")
   c = Application.Trim(c)
Next
 
End Sub

hi,

I can use VBA to download csv files published from google sheets with the function below which works well. However I also want to download csv files from another site that has the following format:

https://www.airbnb.co.uk/transaction_history/csv/XXXXXX?pending=1

(this is the URL that shows in the download history)

However all I get in the attached HTML file. Is there a way I could alter the VBA code to obtain the csv?

Thanks

Function DownloadFile(myURL, saveToPath)

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "username", "password"
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile saveToPath, 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If

End Function

Select all Open in new window

airbnb_txn_pending.csv

I'm creating an excel file from a database using a vbscript.  It all works great... but, in a few columns, I have a very long column name, for example, "Number of Missed Appointments" and I want to put that on 3 lines in a cell so it looks like:
Number of
Missed
Appointments
And I want the cell to autofit to the width of the widest line (in the above example, Appointments).

Maybe this will help convey what I'm trying to do/want:


I'm using:

WorkSheetObject.Cells(2,13).Value = "No of missed" & vbCrLF & "appointments"
WorksheetObject.Cells(1,13).EntireColumn.Autofit

Select all Open in new window

Hi

  I am using acrobat pro 11 to convert a word doc to pdf using vba in word 2013. I need to know how to change the security setting within vba to restrict editing of the pdf doc.
 Thanks.

 Here's my code :

Sub Créer_PDF()
'
' Créer_PDF Macro
'

Dim CurrentFolder As String
Dim FileName As String
Dim myPath As String
Dim UniqueName As Boolean

UniqueName = False

'Store Information About Word File

  myPath = ActiveDocument.FullName
  CurrentFolder = ActiveDocument.Path & "\"
  FileName = Mid(myPath, InStrRev(myPath, "\") + 1, InStrRev(myPath, ".") - InStrRev(myPath, "\") - 1)

  ActiveDocument.ExportAsFixedFormat _
        OutputFileName:=CurrentFolder & FileName & ".pdf", _
        ExportFormat:=wdExportFormatPDF, _
        OpenAfterExport:=False, _
        OptimizeFor:=wdExportOptimizeForPrint, _
        Range:=wdExportAllDocument, _
        From:=1, _
        To:=1, _
        Item:=wdExportDocumentContent, _
        IncludeDocProps:=False, _
        KeepIRM:=False, _
        CreateBookmarks:=wdExportCreateHeadingBookmarks, _
        DocStructureTags:=True, _
        BitmapMissingFonts:=True, _
        UseISO19005_1:=False
End Sub

Select all Open in new window

I had this question after viewing Excel how to update data connection link from cell value.


the attached workbook used to work. now it does not.  i get error access denied on the    .LoadSettings strNewURL

how can i overcome this issue,  if i manually import the same string it will work, but it does not work with the VBA

Sub ChangeDataSource()
Dim tbl As ListObject
 
strNewURL = Sheets("mySheet").Range("A1")
 
Set tbl = Sheets("Data").Range("A1").ListObject
 
With tbl.XmlMap.DataBinding
    If .SourceUrl <> strNewURL Then
        .ClearSettings
        .LoadSettings strNewURL
        .Refresh
    End If
End With

End Sub

Select all Open in new window

EE.xlsb

Hello

I am new to Experts Exchange, I came across it when searching the internet for help with VBA UserForm coding.  I landed on https://www.experts-exchange.com/questions/28706684/VBA-Code-to-Populate-Userform-with-Existing-Records-Edit-them-and-add-them-back-to-the-worksheet.html  which not only answered my search but helped me to complete my UserForm.  Basically, the form on that thread was exactly what I was doing.  So I downloaded the solution and set about completing my form, copying and editing the code.

All was going well considering I haven’t used VBA for years (10+) and my coding skills are a bit rusty.  My problem is when I test my form I get “runtime error 9: subscript out of range” for the SEARCH, ADD and UPDATE buttons.  Set ws = Sheets("Sheet4") seems to be causing the problem.

I’ve also noticed that if I start the form from any other sheet than the sheet with the data in it the form is basically empty.  Since I have set the Workbook to open on the USER GUIDE sheet, as this has a button to open the form, this causes a problem.

Well as I say I’m a bit rusty and I’ve been at this for days, reading and re-reading the code, search after search online as well as VBA Help, and now I think I have gone code blind (can’t see the wood for the trees) and my rusty skills are making it hard to find what’s wrong.  If someone could take a look at the attached file and point me in the right direction I would be most grateful.

The above thread was solved by …