Copy name from Excel Cell, Re arrange to be pasted elsewhere

[qwiqshot]

Is there some way to copy data from a cell (Someones first and last name) then re arrange it place a comma between them and then paste it elsewhere like into notepad?

For example. Cell A2 contents read Gerry Folks - Grab this data, re arrange to read Folks, Gerry - then be able to paste it into, say Notepad

An ex co worker did this in excel VB but I am working to eliminate their code by using Autoit

This was his VB code

1. =LEFT(A2,FIND(" ",A2,1)-1)
2. =RIGHT(A2,LEN(A2)-FIND(" ",A2,1))
3. =CONCATENATE(J18,", ",I18)

[water]

AutoIt comes with an Excel UDF. Use function _Excel_RangeRead to read the cells value into a variable which you then can rearrange. 

[l3ill]

had some time to kill...

Step. 2

6 hours ago, water said:

a variable which you then can rearrange. 

$sVar = "Gerry Folks"

$aNewstring = StringSplit($sVar, " ")
$sNewstring = $aNewstring[2] & ", " & $aNewstring[1]
MsgBox(0, "", $sNewstring)

 

[qwiqshot]

Thank you both. Work got busy and had not had time to reply until now.

[qwiqshot]

Hi again all

I've been tinkering around trying to get this to work and I am not understanding how it works. I have what I think should work, but it is not.

#include <Excel.au3>


Func  getname()
   Local $oExcel = _Excel_Open()
   Local $sResult = _Excel_RangeRead ($oExcel.Worksheets("Calculations"), "A3")
   $aNewstring = StringSplit($sResult, " ")
   $sNewstring = $aNewstring[2] & ", " & $aNewstring[1]

EndFunc

I have attached a small screenshot showing the names of a few tabs on the spreadsheet in case it helps solve my issue. I run the function, then I am opening a notepad and pasting any data that has been captured. I am not getting the data from cell A3 on the Calculations sheet. Please smack me in the right direction.

[water]

You either need to open a workbook using _Excel_BookOpen or connect to an already open workbook by using _Excel_BookAttach.

[qwiqshot]

Is this what your saying Water?

#include <Excel.au3>


Func  getname()

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookAttach($oExcel)
Local $sResult = _Excel_RangeRead ($oExcel.Worksheets("Calculations"), "A3")


$aNewstring = StringSplit($sResult, " ")
$sNewstring = $aNewstring[2] & ", " & $aNewstring[1]
MsgBox(0, "", $sNewstring)

EndFunc

 

Edited September 14, 2016 by qwiqshot

[water]

No. Please read the help file for _Excel_BookAttach and you will see what's wrong with your statement

[qwiqshot]

Hi again Water

Does the example change if the spreadsheet were already going to be open?

Here is the scenario.... I enter in the details of a pre defined excel form, I go to save the file, at this point I want to grab the name from field A3 of sheet named Calculations, flip the names in reverse (Last name 1st, 1st name last),  then add the resulting combination to the file name to be saved. So I would already have the spreadsheet opened and would not require opening anything like in the example (assuming I am reading it correctly, as it shows _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls" in the example).

 

[water]

Functions _Excel_BookAttach connects the script to an already opened worksheet. So: Yes, you need to this function. Please check the example for _Excel_BookAttach.

[qwiqshot]

I tried the help file suggestion and it most definitely gets the data from the cell I want, but it reopens the excel sheet losing the data entered.  This is what I used....

#include <Excel.au3>
#include <MsgBoxConstants.au3>


; Create application object and open an example workbook
Local $sWorkbook = "c:\Excel\AsphaltEstimating.xlsm"
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example", "Error opening workbook '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Attach to the first Workbook where the file path matches
; *****************************************************************************
$oWorkbook = _Excel_BookAttach($sWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 1", "Error attaching to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 1", "Search by 'filepath':" & @CRLF & @CRLF & "Successfully attached to Workbook '" & $sWorkbook & "'." & @CRLF & @CRLF & "Value of cell A3: " & $oWorkbook.Activesheet.Range("A3").Value)

how can I prevent it from closing the sheet losing the data being entered? what I am planning is to save a copy of the excel sheet which is essentially a form with scripts to run calculations to generate contracts, with previously empty fields, now filled with customer specific data entered in, I want to grab the full customer name from A3 to save the file per their name. So if it's closing the file I lose all data entered.....

I know this gets old pointing people in the direction of their answers so I apologize for running anyone in the circles you've likely answered a thousand times.

[water]

Drop this part of your script:

Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example", "Error opening workbook '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

You either need _Excel_BookOpen OR _Excel_BookAttach.

[qwiqshot]

Water

Thank you very much! I would never have thought to drop any of the code. I would have continued trying endless variations until my brain popped. Now i'm excited again!

[water]

[qwiqshot]

I have another step I am trying to figure out now and have no clue where to start.

I have a spreadsheet with 62 tabs. I want to save the file by grabbing the customers name from the file (have this figured out), then after the script enters the file name according to the customers name I want to amend the name with what ever name is in the tab presently selected (being viewed).

For example, I have 62 tabs, their names are below. I grab the customers name from the sheet and then grab the tab name and back to sheet to get address to make the file saved as * Folks, Gerry (X-Pave) 112233 Big Hill Rd 99002* 

I know how to get the name of all my tabs, this is the result, I have no idea how to grab the name from the tab to be sent to the save dialog box

Here is my tab list......

expandcollapse popup

[0]|Material|
[1]|From Leads|
[2]|Calculations|
[3]|Formula|
[4]|Overlay Drive|
[5]|Saw Cut Overlay|
[6]|X-Pave|
[7]|Pave Gravel|
[8]|New Construction|
[9]|Overlay Parking Lot|
[10]|Milling|
[11]|Excavate 2C 2X|
[12]|Speed Bumps|
[13]|X-OVERLAY|
[14]|Excavate - Gravel|
[15]|Extension & Overlay|
[16]|Overlay Pave Gravel|
[17]|Gravel - New Construction|
[18]|Excavate - New Construction|
[19]|Surface Patch|
[20]|Saw Cut Patches|
[21]|Chip & Seal|
[22]|Concrete Bid|
[23]|Drain Blank|
[24]|DS Ground|
[25]|DS Asphalt|
[26]|French Drain Asp|
[27]|French Drain (2)|
[28]|French Drain|
[29]|Channel Ground|
[30]|Channel Asphalt|
[31]|Patch & Seal|
[32]|Res Seal|
[33]|Multi Areas|
[34]|Com Seal Hot Pour|
[35]|Com Seal No Cks|
[36]|Com Seal|
[37]|Com Seal 2|
[38]|HOT POUR|
[39]|Measured Areas|
[40]|Dummy Bid 2|
[41]|Companies|
[42]|Bollards|
[43]|Striping|
[44]|Fax Cover|
[45]|Concrete Cover|
[46]|Res-Seal Coating|
[47]|Com Seal-Coating|
[48]|Repeat Cus|
[49]|Cover Blank|
[50]|Revised Bid Cover|
[51]|Cover|
[52]|Drainage Cover|
[53]|New Res Cover|
[54]|Tennis Court|
[55]|GC Job|
[56]|Blank|
[57]|Referrals|
[58]|Bid Number|
[59]|WorkOrder|
[60]|Blank Work Order|
[61]|Manual|
[62]|Cover Letter|

 

Thanks in advance if anyone knows how

[water]

Why use the save dialog box, try function _Excel_BookSaveAs.

[qwiqshot]

That works too. I still cannot figure out how to grab the name from the currently viewed tab tho...

[water]

Can't test at the moment but I think it should be:

$sName = $oWorkbook.ActiveSheet.Name

 

[qwiqshot]

One last question. I have it working exactly the way I want it to, but it takes a pretty long time to save the file (25 to 30 seconds). If manually save the file it takes very little time to save (2 to 3 seconds). can  this be sped up any?

; Create application object and open an example workbook
Local $sWorkbook = "c:\Excel\AsphaltEstimating.xlsm"
Local $oExcel = _Excel_Open()
$oWorkbook = _Excel_BookAttach($sWorkbook) ; Attach to Workbook
$fName = ($oWorkbook.Worksheets(4).Range("J20").Value) ; Customers name
$cAddress = ($oWorkbook.Worksheets(4).Range("I30").Value) ; customers address
$sFilePath = "\\MYCLOUDEX2ULTRA\Bids\2017 Bids\Mark\"&($fName)&" "&"("&($oWorkbook.ActiveSheet.Name)&")"&($cAddress)

If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Fail", "Error Try Again" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_BookSaveAs($oWorkbook, $sFilePath, $xlWorkbookDefault, True)

If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Fail!", "Error saving workbook to '" & $sFilePath & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Success!", "Workbook successfully saved as '" & $sFilePath & "'.")

The resulting save file name is:

Jones, Jesus (Pave Gravel) 777 Sky Ct Heavenly AB 77777

Jones, Jesus = $fName , Pave Gravel = ($oWorkbook.ActiveSheet.Name) , 777 Sky Ct Heavenly AB 77777 = $cAddress

[water]

I do not see a reason why it should take 30 seconds to save a workbook. Is this the whole script?