Using VBA Code to open a program and then auto select the file to be used in the program

[Spotycus]

Hello,

I am still very new to VBA and will appreciate any assistance or suggestions. I am trying to automate a workflow process that would proceed like this:

The parts:

transactions.xlsx - web generated download that contains my raw data (E:\\downloads\Parkway\transactions.xlsx)
datatemplate.xlsm - macro enabled excel sheet that already handles all my clean up and saves a new file checks.csv in the format needed
Checks.csv - file that needs to be selected by software program after it is opened using VBA.
Checks.csv - E:\\downloads\Parkway\checks.csv
CSV2QBO.exe - command line for second software (E:\\Program Files (x86)\CSV2QBO.exe)
Checkimport.qbo - final product that will be produced after running through software
​

PRE VBA

1. GO to website and manually complete data entry to be able to export an excel file always named transactions.xlsx
2. Open transactions.xlsx and click custom button (already on my custom ribbon) to start VBA process

Starting VBA:

1. Open second workbook datatemplate.xlsm
2. Import cells from transactions.xlsx into datatemplate.xlsm on sheet "rawdata"
3. Data is Manually Verified and Next Macro is started
4. New file checks.csv is created and saved, AND CSV2QBO software is opened.
5. CSV2QBO window opens to choose the file to import
6. NOTE: This is where I need help: I want to automate this part
   1. I want the program to auto select Checks.csv and start the process.
   2. The software will ask you to save and name the file Checkimport.qbo
7. Open Google Chrome to a web address such as https://xyzsite.com

Done.

Current code being used for the final steps:

Option Explicit

#If Win64 Then
Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
#Else
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
#End If

Private Const SW_HIDE As Long = 0
Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMAXIMIZED As Long = 3
Private Const SW_SHOWMINIMIZED As Long = 2

Sub Start_CSV2QBO()
'
' Start_CSV2QBO Macro
' Startup CSV2QBO
'
' Keyboard Shortcut: Ctrl+o
Application.Goto Reference:="worksheetaccount"
Selection.Copy
Application.Goto Reference:="worksheetcoa"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Paste from Ledger").Select
Range("A1").Select
Sheets("QBO Export").Select
Sheets("QBO Export").Copy
ActiveWorkbook.SaveAs Filename:= _
"E:\Downloads\Parkway\Checks.csv", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False

ShellExecute 0, "open", "E:\Program Files (x86)\CSV2QBO\csv2qbo.exe", vbNullString, "E:\", SW_SHOWNORMAL

End Sub

Thank you again in advance!

 

[John_w]

There is no need to open the CSV2QBO application window because it has a command line interface: csv2qbo.exe input.file output.QBO

Replace the ShellExecute line with:

    command = Q("E:\Program Files (x86)\CSV2QBO\csv2qbo.exe") & " " & Q("E:\downloads\Parkway\checks.csv") & " " & Q("E:\downloads\Parkway\Checkimport.qbo")
    
    Set wsh = CreateObject("Wscript.Shell")
    wsh.Run command, 0, True    '0/1 = show/hide command window, True = wait on return

and add this function:

Private Function Q(text As String) As String
     Q = Chr(34) & text & Chr(34)
End Function

Please use CODE tags - the # icon in the message editor.

 

[Spotycus]

Hi John,

First I need to apologize for not saying thank you sooner! Your advice led me down a path which helped me gain a lot of addition knowledge. Unfortunately, that knowledge still does not include proper command line coding, at least for the end result that I desire. Could you please take a look at my updated code to help me understand where I went wrong? I have attempted to change things up many different ways to see if I could get everything to work before asking again so thank you in advance.

First the code, then I will explain where I am stuck:

 Sub Process()
 
    Application.ScreenUpdating = False
    Application.Goto Reference:="worksheetaccount"
    Selection.Copy
    Application.Goto Reference:="worksheetcoa"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Application.Goto Reference:="input"
    Selection.Copy
    Application.Goto Reference:="QBO_output"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
     Application.Goto Reference:="QBO_Transfer"
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
     
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$20"), , xlYes).Name = _
        "Checklist"
    Range("Checklist[#All]").Select
    
    ChDir "C:\Parkway"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="C:\Parkway\checkimport.xls" _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    Application.Goto Reference:="Inputarea"
    Application.ScreenUpdating = True
    
    Command = Q("C:\Parkway\csv2ofx.exe") & " " & Q("C:\Parkway\checkimport.xls") & " " & Q("C:\Parkway\checkimport.ofx")
    
    Set wsh = CreateObject("Wscript.Shell")
    wsh.Run Command, 0, True    '0/1 = show/hide command window, True = wait on return
    
    End Sub
    
     Private Function Q(text As String) As String
     Q = Chr(34) & text & Chr(34)
     End Function

As you can see a lot has changed with the code but the goal is still the same. I have triple checked the locations of all the command line items and everything is exactly where the addresses point to. Will it make a difference that I moved the csv2ofx.exe file out of the Program (x86) file? I have tested calling the program and it opens without a problem using my old shell method.

Right now when I run the code I receive a missing object error message.

MY GOAL: Be able to use the command line to make the whole conversion happen without any user input or additional programs showing.

Using my prior Shell method I was able to call the program have it open and then manually select the file, click convert to process and finally save the ofx file and then close the program. BUT like you suggested, what I really want to be able to do is to run the script and have it auto select the file ("\checkimport.xls") and use the define mapping [Parkway] to generate the output file ("\checkimport.ofx").

Can you help me figure out where I went wrong?

For extra YOU ROCK points:

I plan to create a popup menu that says "Your file has completed, Would like to import now?" Button yes will link to https://parkway.business.dataimport and button No will close workbook without saving. If you have some code to share toward this idea, it would be appreciated but even a push in the right direction will help.

 

[John_w]

I see you're now running a different executable, csv2ofx.exe. I installed the trial version, but running it from the command line did not produce an output file. For example, running the following command manually from a Windows command prompt window:

csv2ofx.exe sample.csv sample_ofx.ofx

doesn't create the expected sample_ofx.ofx. Converting with the UI works OK.

Here is code which attempts to run the command on the sample data in the code workbook:

Public Sub Test()

    Dim folderPath As String, csvFile As String, ofxFile As String
    Dim command As String
    Dim wsh As Object
        
    folderPath = "C:\Temp\excel\csv2ofx\"
    csvFile = folderPath & "checkimport CSV.csv"
    ofxFile = folderPath & "checkimport OFX.ofx"
    
    Application.DisplayAlerts = False
    Save_Sheet_As_CSV ActiveSheet, csvFile
    Application.DisplayAlerts = True
    
    command = Q("C:\Program Files\ProperSoft\CSV2OFX\csv2ofx.exe") & " " & Q(csvFile) & " " & Q(ofxFile)
        
    Set wsh = CreateObject("Wscript.Shell")
    wsh.Run command, 1, True    '0/1 = show/hide command window, True = wait on return

    'Also try Shell function
    'Shell command, vbNormalFocus

End Sub

Private Function Q(text As String) As String
    Q = Chr(34) & text & Chr(34)
End Function

Private Sub Save_Sheet_As_CSV(ws As Worksheet, fileName As String)
    ws.Copy
    ActiveWorkbook.SaveAs fileName, FileFormat:=xlCSV
    ActiveWorkbook.Close False
End Sub

But, as I say, this produces no output, nor any errors. Because of that, I can't really help further.

I don't think it matters if the csv2ofx.exe isn't in Program Files because the setup only installs that program.

For your final question, what do you mean by "link to"? If you mean open the IE browser on that web page, then try something like:

    Dim response As Variant
    response = MsgBox("Your file has completed, Would you like to import now?", vbYesNo)
    If response = vbYes Then
        With CreateObject("InternetExplorer.Application")
            .navigate "https://parkway.business.dataimport/"
        End With
    Else
        ThisWorkbook.Close False
    End If

 

[Spotycus]

Thank you so much for all the extra help!! I will give your code a try this evening and let you know how it worked. Since I have the whole software bundle, I will also try out the original csv2qbo to see if that works better, I can always change the other parts of the process as needed. Thank you again!!