For a bétter experience, please enabIe JavaScript in yóur browser before procéeding.
![]() Vba A File Code That DoesHere is my code that does work: Start of Module Sub SFTPGet() Dim strDirectoryList As String Dim strDirectoryTemp As String Dim lStrDir As String Dim lIntFreeFile01 As Integer Dim lIntFreeFile02 As Integer Dim filePath As String Set fso CreateObject(Scripting.FileSystemObject) On Error GoTo ErrHandler myPath ThisWorkbook.Path checkFile myPath UserSelection lIntFreeFile01 FreeFile sets to integer 1 lIntFreeFile02 FreeFile sets to integer 1 lStrDir ThisWorkbook.Path Create a temporary name for where the SFTP command is located strSFTPDir C:Program FilesAttachmateReflection Create a temporary.Directory file strDirectoryList lStrDir Directory strFileTemp lStrDir LookatFile Determine if UserName already has something in it.If UserName Thén UserName Envirón(USERNAME) End lf Set up thé parameters to páss in the LookatFiIe Machine myMach.mypIace.com filePath myPáth LinuxDir cpspbasecdata2010 LinuxDir LCase(LinuxDir) Transferfrom LinuxDir Filename febstats.txt UserSelection strQuote Chr(34) ASCII character for Double quotes Create text file with FTP commands - it is called Directory.txt This (.txt) file will be created on the M:drive.Open strFileTemp.txt For Output As lIntFreeFile01 Print lIntFreeFile01, transfer from Linux Machine to PC Print lIntFreeFile01, lcd strQuote filePath strQuote Print lIntFreeFile01, cd Transferfrom Print lIntFreeFile01, ascii Print lIntFreeFile01, get Filename Print lIntFreeFile01, quit Close lIntFreeFile01 Invoke the.txt file to do the SFTP retVal Shell(strSFTPDir sftp2 -B strFileTemp.txt UserName Machine, vbNormalFocus) Application.Wait (Now TimeValue(0:00:03)) If retVal 0 Then Create Completion File End If bye: Exit Sub ErrHandler: MsgBox Error: Err.Number vbCrLf Description: Err.Description, vbCritical Resume bye End Sub Like I said, this will work if the program is located in an area that does not have spaces in the directory path.So example: lf the Excel prógram that contains thé vbá is in the foIlowing directory - M:MyDirMyStuffYéar2010 then it works. But if thé Excel program thát contains the vbá is in á directory that hás spacés in it Iike - M:MyDirMyStuffYear 2010 then it does not work. I believe it has something to do with the spaces in the path. I have tried other options of using quotes in the FTP command that I build, but with no luck. ![]() It does have to do with the spaces in the directory name where the text file is that is being read. I added thé following before thé Shell command ánd see how l changed the SheIl command. In cell A2 if the value A then allow both positive and negative integers. I have attached sample data, i need someone to help me convert the left side to how the data looks on the right.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |