docmd

[Legacy 15162]

what reference library do i need to use docmd.setwarning true? or to run a dos command from excel vba?

 

[parry]

What exactly are you trying to do? DoCmd is something used in Access not Excel. Whats that got to do with a DOS command?

If your actually wanting to start DOS then use shell

Shell "C:\WINNT\system32\cmd.exe"

Excel has alertcodes that come up for various things. Are you trying to get rid of a warning message? Pease give some more detail.

 

[Legacy 15162]

nope, trying to run this command line
pdfMachineCmdLine \\test-sql\test.xls \\test-sql\test.pdf

 

[Norie]

txksa

The Shell command suggested by Parry sounds like what you are looking for. Check out the Excel VBA help file.

Have you tried something like this:

Dim retval

retval = Shell("pdfMachineCmdLine \\test-sql\test.xls \\test-sql\test.pdf")

 

[Legacy 15162]

I am getting a runtime error 53 "file not found" do i need to tell it where pdfmachinecmdline.exe is located?

Set xlOrigWB = ActiveWorkbook
For i = 1 To xlOrigWB.Worksheets.Count
Set xlCopyWB = xlOrigWB
Set xlWS = xlCopyWB.Worksheets(i)
stWkshtName = xlWS.Name
If stWkshtName <> "APC" And stWkshtName <> "LA-TX" And stWkshtName <> "Carolinas" And stWkshtName <> "Cen LA" And stWkshtName <> "South LA" And stWkshtName <> "West Laf" And stWkshtName <> "Midland" And stWkshtName <> "Odessa" And stWkshtName <> "N Charlotte" And stWkshtName <> "SW Charlotte" And stWkshtName <> "E Charlotte" And stWkshtName <> "W Charlotte" And stWkshtName <> "C Charlotte" And stWkshtName <> "CONTROL" And stWkshtName <> "Administrative" Then
xlWS.Copy
' lngPeriod = Right(xlWS.Cells("C1"), 2)
Set xlCopyWB = ActiveWorkbook
NewFileName = "\\test-sql\Poll\StoreUpld\" & stWkshtName & "pnlp" & stPeriod
xlCopyWB.SaveAs (NewFileName)
'xlWS.Cells.Copy
'xlWS.Cells.Select
'xlWS.Cells.PasteSpecial (xlPasteValues)
xlCopyWB.Close
retval = Shell("pdfMachineCmdLine " & NewFileName & ".xls " & NewFileName & ".pdf")

End If
Next i

 

[Legacy 15162]

to answer my own question.....YES, i do have to tell it the location of the pdfmachinecmdline.exe

 

[Norie]

I am getting a runtime error 53 "file not found" do i need to tell it where pdfmachinecmdline.exe is located?

Yes.

BTW why not try a Select Case statement instead of the If Then:

Set xlOrigWB = ActiveWorkbook

For I = 1 To xlOrigWB.Worksheets.Count
    Set xlCopyWB = xlOrigWB
    Set xlWS = xlCopyWB.Worksheets(I)
    stWkshtName = xlWS.Name
    
    Select Case stWkshtName
    
        Case "APC", "LA-TX", "Carolinas", "Cen LA", "South LA", "West Laf", _
            "Midland", "Odessa", "N Charlotte", "SW Charlotte", "E Charlotte", _
            "W Charlotte", "C Charlotte", "CONTROL", "Administrative"
            ' do nothing
        Case Else
            xlWS.Copy
            ' lngPeriod = Right(xlWS.Cells("C1"), 2)
            Set xlCopyWB = ActiveWorkbook
            
            NewFileName = "\\test-sql\Poll\StoreUpld\" & stWkshtName & "pnlp" & stPeriod
            xlCopyWB.SaveAs (NewFileName)
            'xlWS.Cells.Copy
            'xlWS.Cells.Select
            'xlWS.Cells.PasteSpecial (xlPasteValues)
            xlCopyWB.Close
            retval = Shell("pdfMachineCmdLine " & NewFileName & ".xls " & NewFileName & ".pdf")
    
    End Select
    
Next I