Non Parameterized URL Format JD Edwards EnterpriseOne

Non Parameterized URL Format JD Edwards EnterpriseOne

http://FullServerName/jde/HostedE1Servlet?OID=P5614010_W5614010X

Excel VBA 2010 - Read a folder and load file name in SQL-Server 2008

Sub ListAllFile()

     

    Dim objFSO As Object

    Dim objFolder As Object

    Dim objFile As Object

    Dim ws As Worksheet

    Dim cnt As Integer

    

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    'Set ws = Worksheets.Add

    'ActiveSheet.Range("C1").Value

    Set ws = Worksheets("Sheet1")

    cnt = 1

     

     'Get the folder object associated with the directory

    Set objFolder = objFSO.GetFolder("\\abcserver\Fin\Report Output")

    ws.Cells(1, 1).Value = "The files found in This Folder are:- " ' & objFolder.Name & "are:"

    

     'Loop through the Files collection

    For Each objFile In objFolder.Files

        ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = objFile.Name

        'Debug.Print objFile

        rptPathName = objFile.Name

        Call InsertRecord(cnt, rptPathName)

    cnt = cnt + 1

    Next

     

     'Clean up!

    Set objFolder = Nothing

    Set objFile = Nothing

    Set objFSO = Nothing

     

End Sub

Sub InsertRecord(strcnt, strFilePathName)

            Dim adoCN As ADODB.Connection

            Dim sConnString As String

            Dim sSQL As String

            Dim lRow As Long, lCol As Long

            Dim recnt As Integer

            Dim RecRow As Integer

            Dim tbl As Range

            Dim FileName

            Set adoCN = CreateObject("ADODB.Connection")

            sConnString = stADO

            adoCN.Open sConnString

            FileName = strFilePathName

            strFilePathName = ActiveSheet.Range("C1").Value & "\" & strFilePathName

            

                         Dim MyString As String

                                        sSQL = "insert into budget.dbo.tbltempReport  select " & strcnt & ",'" & Replace(strFilePathName, "'", "''") & "'" & ""

                                       Debug.Print sSQL

                          adoCN.Execute sSQL

            adoCN.Close

            Set adoCN = Nothing

End Sub

Table structure

CREATE TABLE [dbo].[tbltempReportOutPD](

[ID] [int] NULL,

[ReportName] [nvarchar](1000) NULL

) ON [PRIMARY]

Shutdown PC command line

  shutdown /s

Run VBA reports in Multiple Instances - Shell

Sub RunExcelMultiple(RecCnt)

    Dim lPid As Long, i As Long

    Dim sRemoteWbName As String, sExeName As String, sParams As String

    sExeName = "excel.exe"

    

    sRemoteWbName = """" & ActiveWorkbook.FullName & """"

    

    sParams = " /e" & " -!- " & " "

    

    If (RecCnt < 11) Then

      lPid = Shell(sExeName & sParams & sRemoteWbName, vbHide)

    Else

        For ShellInstance = 1 To Round(WorksheetFunction.Min(8, RecCnt / 4), 0)

            lPid = Shell(sExeName & sParams & sRemoteWbName, vbHide)

        Next ShellInstance

    End If

    

       

End Sub