docmd

L

Legacy 15162

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

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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.
 
L

Legacy 15162

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
Dim retval

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

Legacy 15162

Guest

ADVERTISEMENT

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
 
L

Legacy 15162

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
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
 

Forum statistics

Threads
1,147,622
Messages
5,742,205
Members
423,711
Latest member
luisfreitas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top