Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 15th June 2004, 17:30
isison's Avatar
isison isison is offline
Junior Member
 
Join Date: Feb 2003
Posts: 3
isison is on a distinguished road
Baan: B4c2 - DB: Oracle - OS: Win2000
Excel Macro

I've created an excel spreadsheet containing a macro which would format the file and stored it in the network. I've also created a printer device in Baan that will open excel including the spreadsheet containing the macro. It seemed to work ok, excel opens with the data, but the problem is instead of running the macro, it overwrites the spreadsheet with the data from Baan, deleting the macro altogether. Does anybody have any insight?

I've read a lot of messages on this but was not really very clear on the macro part.

Thanks.
__________________
isison
Reply With Quote
Sponsored Links
  #2  
Old 15th June 2004, 17:57
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,905
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Here is how it works here. The user runs a report and selects my macro device. The device then downloads the report to a pre-determined location (example: c\baan\temp\tisfc1410000.txt"). I then launch excel with the macro. The macro opens the predetermined text file, formats it, etc. Then saves it the new file as a excel spreadsheet. This prevents the macro from being over-written. I actually have a table that holds the report and where to download it, and which macro to run to format it.

Mark
Reply With Quote
  #3  
Old 15th June 2004, 19:18
isison's Avatar
isison isison is offline
Junior Member
 
Join Date: Feb 2003
Posts: 3
isison is on a distinguished road
Baan: B4c2 - DB: Oracle - OS: Win2000
Walk through

So if I understand it correctly here's how you set it up:
1. Baan Printer Device (ex. B2EXCEL) with path = location of the excel spreadsheet with macro.
2. Alternative program to ttstpconv that starts the excel application using the command app_start("<excel_path spreadsheet_with_macro_path>",...)
3. Macro in excel copies the file to a different file name?

I've done steps 1 and 2 but my macro doesn't run, instead, it overwrites the file with the ascii file from Baan. I must be doing something wrong.
__________________
isison
Reply With Quote
  #4  
Old 15th June 2004, 20:06
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,905
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Simple walk through
(1) User runs session calls my device (PCEXCEL) running new ttstpconv called tuddcconv1
(2) tuddcconv1 looks up in table tuddc905 report name like rtisfc960001000, finds download location with filename(C:\Program Files\Baan\tmp\perfdetails.txt), macro location(m:\data\excel\baseline\) and macro name(tisfc9600m000d.xls).
(3) tuddcconv1 runs ttstpconv with ascii to temp file.
(4) tuddcconv1 runs server2client to download file. (Creates C:\Program Files\Baan\tmp\perfdetails.txt)
(5) tuddcconv1 runs excel with macro file name.(m:\data\excel\baseline\tisfc9600m000d.xls
(6) Each macro has something like this in it - we keep macros in a common directory on a file server.
Code:
Sub auto_open()
    Call Performance_details
End Sub
Sub Performance_details()
'
' Baseline Macro
' Macro recorded 3/2/2001
'Step 1 Convert text to columns
    Set fs = CreateObject("Scripting.FileSystemObject")
    If Not fs.fileexists("C:\Program Files\Baan\tmp\perfdetails.txt") Then
        Exit Sub
    End If
    Workbooks.OpenText FileName:="C:\Program Files\Baan\tmp\perfdetails.txt", Origin:= _
        xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
        Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
        , 1), Array(8, 2), Array(9, 1), Array(10, 1))
    Sheets(1).Name = "Details"
' Save as for the sub-totals
    ChDir ("C:\Program Files\Baan\tmp")
FileSaveName = Application.GetSaveAsFilename(initialfilename:="perfdetails.xls", _
                filefilter:="Microsoft Excel Workbook (*.xls),*.xls")
' Step 7  Do save as to allow user to save worksheet
    ActiveWorkbook.SaveAs FileName:=FileSaveName, _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
At no point do I write anything to the macro spreadsheet. Make sure your macro actually works on the imported text file rather than in the macro spreadsheet. As a matter of fact our macros are usually saved as RO so the users can not hose them up. Also note I left out all the crap between opening and saving the spreadsheet. It sounds like your macro is actually the piece that is not working. Also forgot that we actually have another table that lets us run excel from various locations for each site and it also allows us to have mutliple macros for the same report - different formats for different users at different sites.


Mark
Reply With Quote
  #5  
Old 15th June 2004, 20:46
isison's Avatar
isison isison is offline
Junior Member
 
Join Date: Feb 2003
Posts: 3
isison is on a distinguished road
Baan: B4c2 - DB: Oracle - OS: Win2000
Thanks for your help. I will try your suggestions.
__________________
isison
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
CODE: Creating your own ttstpconv Sample mark_h Code & Utilities 54 13th June 2016 11:41
Can we record user actions like in excel macro? anupmaduskar Enhancements 1 1st March 2004 15:54
Extracting Baan using Macro Excel renyc95 Third Party Products 0 22nd August 2003 05:35
Help for create macro in Excel for baan amtolo AFS/DDC/OLE: Function servers 2 7th February 2003 14:48
Baan to Excel, Excel gets from Baan ??? BaanTech Tools Development 1 15th November 2002 22:51


All times are GMT +2. The time now is 08:36.


©2001-2017 - Baanboard.com - Baanforums.com