Author Topic: Back in the saddle.... Excel, Spanner, VBA solution...F1  (Read 2974 times)

0 Members and 1 Guest are viewing this topic.

akdrafter

  • Guest
Back in the saddle.... Excel, Spanner, VBA solution...F1
« on: November 05, 2006, 02:16:18 AM »
Hello All,

Back in the saddle again. Some bosses....sheesh and man, some engineers hate it when you tell em their wrong. Oops, wait a minute, I am the wrong one... I am the cad guy. Darn, forgot that part. :lmao: Oh well, live, learn and move on to bigger and better things.

I have used Spanner from (wfcadtools_dot_com) in the past for excel spreadsheet import and modifications with excellent results. I now have some users in version 2006 and soon 2007 & 2008 that say they can't use spanner in 2006. Not sure why and I have not had the chance to explore the reason why. I am hoping that maybe someone might know of this problem and if so, if they have found a way around it. I only found out about this on Friday afternoon and if I can't get this to work I may seek another alternative. Hopefully one that does not cost buko dollar$. I have checked the wfcadtools_dot_com website and it appears the only working versions are up to 2004. Anyone have any info on this? Are there any better low/no cost alternatives to excel spread sheet importation/modification...etc...etc...etc...

Thanks in advance.

Dnereb

  • Guest
Re: Back in the saddle.... Excel, Spanner, VBA solution...F1
« Reply #1 on: November 05, 2006, 03:17:03 PM »
Once you know how to open te excel late bound in a decent manner and to close it of again nicely you can do virtually do anything with it through VBA or VB, C++ and other languages for that matter.
Importing and exporting data can be done this way without paying for software, but it will consume time.

The basics, on the fly:

Code: [Select]
Option Explicit
Const WorkBookName = "TestThis.Xls"
Const ImportPath = "C:\Test\"
Const DataSheet = "ImportDataSheet"

Sub ExclHandler()

Dim Excl As Excel.Application
Dim Wb As Excel.Workbook
Dim Ws As Excel.Worksheet

Dim ExcelWasOpen As Boolean
Dim WorkBookFound As Boolean

'Get an instance of excel if it is open that one to save resources.
On Error GoTo NoExcelOpen
ExcelWasOpen = True
Set Excl = GetObject(, "Excel.Application")
On Error GoTo 0


'Search for the Workbook if Excel was already open (it might be)
If ExcelWasOpen Then
    For Each Wb In Excl.Workbooks
        If Wb.Name = WorkBookName Then
            WorkBookFound = True
            Exit For
        End If
    Next
    If Not WorkBookFound Then
        Set Wb = Excl.Workbooks.Open(ImportPath & WorkBookName)
    End If
Else
    Set Wb = Excl.Workbooks.Open(ImportPath & WorkBookName)
End If

Set Ws = Wb.Sheets(DataSheet)

'Do whatever you want read or write to it.
'make it look nice
'to prove my point write something
Ws.Cells(1, 1).Value = "Driesign.nl"
'and read it into a msgbox
MsgBox Ws.Cells(1, 1).Value, vbCritical, "Stupid Test"
'............
'............
'............
'............
'............


'finish by cleaning up
Set Ws = Nothing
If workbookwasopen Then
    Wb.Save
Else
    Wb.Close True, "Optional Alternative Filename.xls"
End If
Set Wb = Nothing
If Not ExcelWasOpen Then
    Excl.Quit
End If
Set Excl = Nothing

Exit Sub
NoExcelOpen:
ExcelWasOpen = False
Set Excl = CreateObject("Excel.Application")
Resume Next

End Sub

Have fun with it.....

Murphy

  • Guest
Re: Back in the saddle.... Excel, Spanner, VBA solution...F1
« Reply #2 on: November 06, 2006, 05:13:11 AM »
If angelfire had left my site up I could direct you there.
Write me a PM with your email address and I can send you AutosXeduLe.
It does just what you want but, the user has to manually update (press
a button in Excel) when changes are made.

Dnereb

  • Guest
Re: Back in the saddle.... Excel, Spanner, VBA solution...F1
« Reply #3 on: November 09, 2006, 03:48:56 PM »
the fun about the methode i gave you is in updating and controlling excell whenever you want.
but you need to get involved in vb(a) to do so