Running Excel Script from within Alpha Anywhere.

[Zradene]

Hi Guys, currently I am running a script within Excel to modify an Ebay sales report, but I would like to run this from a script from within my desktop application.
What is does is open an Ebay report file, which needs to be modified before I import it into my system.
Below is what I have currently but I need to modify it to make in run within Alpha Anywhere.

Many thanks
Fred

Sub test()
Dim fn As String, e, wsName As String
fn = Application.GetOpenFilename("CSVFiles,*.csv")
If fn = "False" Then Exit Sub
wsName = "NewOrders"
If Not Evaluate("isref('" & wsName & "'!a1)") Then Sheets.Add.Name = wsName
With Sheets(wsName)
.UsedRange.ClearContents
With .QueryTables.Add("TEXT;" & fn, Range("a1"))
.TextFilePlatform = 65001
.TextFileCommaDelimiter = True
.Refresh
.Delete
End With
With .Range("a" & Rows.Count).End(xlUp)
If .Row > 2 Then .Offset(-1).Resize(2).EntireRow.Delete
End With
Union(.Rows(1), .Rows(3)).EntireRow.Delete
For Each e In Array(8, 18)
.Columns(e).Replace "ebay*", "", 2
Next
.Copy
End With
With ActiveWorkbook
.Sheets(1).Name = "Orders"
.SaveAs "C:\Users\Fred\Desktop\Orders.xls", 56
End With
End Sub

[madtowng]

hey...same person, new thread.
I work with spreadsheets often, and I thought your ebay report would be easier to parse without excel, but I need
to see how the data is formatted before I can do much of anything.

[Zradene]

Hi. I will give you a list of all the modifications that take place in the macro script.

[Zradene]

I will also send you the excel csv formated sheet first thing in the morning.

[Zradene]

Basically. Select an eBay csv file from a certain location. Carry out the following changes. A. Change the format of 2 column to numeric. B. Delete blank rows 1 and 3. C. Delete last 2 rows of sheet. D. Delete additional unwanted ebay ref data from 2 columns. Ie. ebay6543876 from Buyer Address2 and Post Address2. E. Rename the sheet to Orders and save the file to a set location in csv format.

[Zradene]

The script in my first post on this thread works perfectly within excel, but it would be great if I run this from within Alpha Anywhere. After I run the script, the saved file "Orders" is imported. I am working on a system that imports ebay sales data, so the client can then print invoices and automatically update stock levels etc. All working very well within Alpha Anywhere.

[Zradene]

Hi, it's seems I am not permitted to upload a CSV file ?

[madtowng]

If you want, feel free to send it to : [email protected].

[Zradene]

Hi, thank you my friend. I will do this right now.

[Ronald Anusiewicz]

Fred, Been a long time since I did anything with Excel and Alpha.

Using xBasic, one can totally manipulate the Excel spreadsheet. Attached is an xbasic script I wrote back in 2011. The script is used to import from an Excel file customer information allowing the user to:

1. Select an Excel file
2. Select the workbook sheets
3. Select the specific cell to be imported

When the script is run, the data from the selected Excel workbook, sheets, cells is copied in this case to a temporary table.

As this was coded 12 years ago, I’m sure improvements could be incorporated. My intention is to give you an example of the power of Alpha and how using xbasic, one can manipulate Excel files.

'Date Created: 22-Nov-2011 10:41:49 PM
'Last Updated: 25-Jun-2023 10:09:43 AM
'Created By  : Ron
'Updated By  : Ron

DIM Global vcExcel_File as c

DIM SHARED vDlg_Title as C
DIM SHARED vMsg as C
vMsg="Select the Excel File, the Workbook Sheets to be Imported and Verify the Cell Locations"
DIM SHARED varC_result as C
DIM XDialogStyle as P
XDialogStyle.AccentColor = "Off White"
XDialogStyle.Color = "Blue White"
vDlg_Title="Update the Customer Database from the Excel Spreadsheet, Verify Data Cells"
dlg_text = <<%dlg%
{Windowstyle=Gradient Horizontal}
{font=Arial,12,b}{units=F}{xmargin=4,4}{ysize=.3};

{text=%H=R%90,2vMsg};;
{font=Arial,12}
{region}
{font=Arial,12,b}
Select Excel File:|{font=Arial,12} [%fExcel Files (*.xls*)%.100vcExcel_File];;;
{endregion};
{region}
{font=Arial,12,b}
First Sheet:|{font=Arial,12} [.6vnSheet_First]
{font=Arial,12,b}  Last Sheet:|{font=Arial,12} [.6vnSheet_Last];;
{endregion};
{region}
{font=Arial,12,b} ID:|{font=Arial,12} [.6vcID_Cell]
{font=Arial,12,b} Name:|{font=Arial,12} [.6vcName_Cell]
{font=Arial,12,b} Street:|{font=Arial,12} [.6vcStreet_Cell]
{font=Arial,12,b} City, State, Zip:|{font=Arial,12} [.6vcCityStZip_Cell]
{font=Arial,12,b} Phone:|{font=Arial,12} [.6vcPhone_Cell]
{font=Arial,12,b} Fax:|{font=Arial,12} [.6vcFax_Cell];;
{endregion};
{line=0};;;{justify=center,Center}
<12,1.5OK>{sp=2}
 <*12,1.5Cancel>{sp=2};;
%dlg%

Start_Over:

varC_result=ui_dlg_box(vdlg_title, dlg_text, <<%code%
%code%)

IF varC_result <> "OK" THEN
    END
END IF

IF vcExcel_File = "" THEN
    ui_msg_box("Error", "Please Select an Excel File ", ui_stop_symbol)
    goto Start_Over
END IF

hourglass_cursor(.t.)
'Default comment text.
DIM GLOBAL pDlg1 as waitdialog
pDlg1.Set_Title("Please Wait")
pDlg1.Create(1,"Repeating")
pDlg1.Set_Message("Updating Customer List")
pDlg1.Set_Bottom_Message("")

dim t as P
t = table.open("cust_tmp")
t.zap(.t.)
t.close()

'declare vars for your data
Dim vcID as c
Dim vcName as c
DIM vcStreet as C
Dim vcCityStZip as c
DIM vcPhone as C
Dim vcFax as c

'open excel application
Dim xlApp as p
xlApp = ole.GetObject("","Excel.Application")

'open your workbook
xlApp.Workbooks.Open(vcExcel_File) 'enter your excel filepath here
xlApp.DisplayAlerts = .F.
'xlApp.Visible = .T. 'comment this line out if you don't want to see Excel

on error goto skip
dim tbl as P
tbl = table.open("cust_tmp")

dim x as N
x = 0
'get data from the selected sheets in the workbook
FOR i = vnSheet_First TO vnSheet_Last

    xlApp.sheets(i).select()
    xlApp.sheets(i).range(vcName_Cell).select()
    vcName = xlApp.activecell.value()    
    xlApp.sheets(i).range(vcID_Cell).select()
    vcID = xlApp.activecell.value()
    xlApp.sheets(i).range(vcStreet_Cell).select()
    vcStreet = xlApp.activecell.value()
    xlApp.sheets(i).range(vcCityStZip_Cell).select()    
    vcCityStZip = xlApp.activecell.value()    
    xlApp.sheets(i).range(vcPhone_Cell).select()
    vcPhone = xlApp.activecell.value()
    xlApp.sheets(i).range(vcFax_Cell).select()
    vcFax = xlApp.activecell.value()

    ''  ... Do something with your data in cMyCell1 and cMyCell2...
    'ui_msg_box(vcID,vcWD+" for "+vcMonth)
    tbl.enter_begin(.t.)
    tbl.name = vcName
    tbl.terminal_id = vcID
    tbl.address = vcStreet
    tbl.citystzip = vcCityStZip
    tbl.phone = vcPhone
    tbl.fax = vcFax
    tbl.enter_end(.t.)
    x = x + 1
NEXT i
on error goto 0
skip:
tbl.close()

'close excel
xlApp.Workbooks.close()
xlApp.DisplayAlerts = .T.
xlApp.Quit()

''append the tmp table to the cust table
a_tbl = table.open("cust")
append.t_db = "cust_tmp.DBF"
append.m_key = "TERMINAL_ID-NAME-ADDRESS"
append.t_key = "TERMINAL_ID-NAME-ADDRESS"
append.m_filter = ""
append.t_filter = ""
append.type = "Unique only"
append.m_count = 7
append.m_field1 = "Terminal_Id"
append.m_exp1 = "@Cust_Tmp->Terminal_Id"
append.m_field2 = "Name"
append.m_exp2 = "@Cust_Tmp->Name"
append.m_field3 = "Address"
append.m_exp3 = "@Cust_Tmp->Address"
append.m_field4 = "Citystzip"
append.m_exp4 = "@Cust_Tmp->Citystzip"
append.m_field5 = "Phone"
append.m_exp5 = "@Cust_Tmp->Phone"
append.m_field6 = "Fax"
append.m_exp6 = "@Cust_Tmp->Fax"
append.m_field7 = "Commision"
append.m_exp7 = "@Cust_Tmp->Commision"
append.t_count = 0
a_tbl.append()
a_tbl.close()

'Default comment text.
pDlg1.Close()
hourglass_cursor(.f.)


ui_msg_box("Success",x+" Customers Were Processed.  Duplicates Were Ignored.  Verify Commissions.",ui_information_symbol)

END​

[Zradene]

Hi Ronald, this looks very interesting. I will take a look at the code right now. Thank you so much for your efforts.

[Mike Wilson]

Hi Fred,
The overall concept of what you are engaged in is most commonly referred to as "Integration". The system I have developed has many bi-directional integrations with many different vendors through CSV data uploads and downloads. Much of the data returned from vendors is "dirty" and requires a significant amount of "massaging" of the data before the data is finalized. What you are doing is exactly this, although I do all the "massaging" of the "dirty" data in Alpha. I do this by importing the complete "dirty" data to temporary tables and perform the fixing of the data in the temporary tables, then finalize the data into the permanent tables, and emptying the temporary tables. I do it this way for the exact reason that it all happens within Alpha, and I can do much more exacting actions to the data in Alpha than in Excel. If you would be interested in transitioning to such an approach, I would be happy to help. Send me a copy of the dirty data CSV and an example of what you want the outcome data to be, to ([email protected]) and I'll show you how that works.

[madtowng]

Sorry about the delay. I had an end of life celebration and other things to deal with.
Based on the file you sent , you can process the data quite easily without using excel.

My guess is someone is manually downloading the data. Have you tried using
ebay apis eBay Developers Program ?

[Zradene]

Hi my friend, I was not aware that this developer program was available. So you are saying I can modify that will at source ? sounds interesting....

[madtowng]

There are 2 parts.
1st, you can download the data automatically.
2nd, you can process the data with no need to use excel(you can do this part even without using the api).