vb to excel

I have written a program in VB 6 which take simple data and saves it to a text file. I have then written a macro in Excel to import the data and do various things with it. I want to use this over a
network, therefore need a multiuser function so more than 1 person can access the saved file at once. So I want to save the data directly to an Excel spreadsheet, but so each variable goes into a specific cell.

This is the problem I am having as I can get it to save to the spreadsheet, just not in seperate cells, it puts all the variables in the same cell.

Anyhelp would be great .
gingeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rondeaujCommented:
Try this

Dim MyexcelSheet as Excel.Worksheet

set Myexcelsheet = getobject(filename)

     from this point you should be able to do want ever you need done. You must make a ref. to the excel 8.0 object in your project before doing this.
0
gingeAuthor Commented:
Well When you set the application visible value to true it hasn't opened the .Xls sheet at all and also you have to open excel which takes an awful amount of time. What I want is to be able to save straight to an excel spreadsheet just like a text file as some of the computers using this app won't even have excel on them but still need to be able to save to the file, so can'topen it like this.

Please help !!!!!!!

Ginge
0
gingeAuthor Commented:
Also could you please go into a little more detail as I don't have much experience in this aspect of VB.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

vbWayneCommented:
how are you running the macro...ie..your macro grabs the data from the text file and then does it's stuff and then you can save the file inside your macro and then users can open the saved .xls file.
for example...

'put this code inside the form load event of the Application
'change the file name and macro name to reflect your file and macro
'this will open Excel and run the macro
'
'create and object (Excel SpreadSheet)
      Dim oXL As Object
      Set oXL = CreateObject("Excel.Application")
     
' Open the workbook that contains the macro to run.
      oXL.Workbooks.open "C:\My Documents\try.xls"
'
'as object opens invisible, make visible if needed, if not omit
'the line oXL.visible=true
'
      oXL.Visible = True
'
' Run the macro.
      oXL.Application.Run "try.xls!Macro1"
'
' Quit Microsoft Excel.
      oXL.Quit
'
' Free the object from memory.
      Set oXL = Nothing

0
rondeaujCommented:
If you do not want to use excel as an object you could learn XML with is the format excel uses to save it's documents
0
paul_tsekovCommented:
Hello,


' Let's say you have the
' variables Var1,Var2,Var3,Var4
' Warning:
' When the type of the variable is
' String you shouldn't convert it
' with CStr()   !!!!!!

' Now try this code for opening
' some XLS file and filling CELLS
' with your variables.
' If you want to create new
' XLS file, you could use
' CreateObject(), instead of GetObject()

' This is your form's module
Option Explicit
Dim Var1 As Single
Dim Var2 As Integer
Dim Var3 As Long
Dim Var4 As String
Dim xlObj As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet

Private Sub FillSheet()
    xlWs.Cells(1, 1).Value = "100"
    xlWs.Cells(1, 2).Value = CStr(Var1)
    xlWs.Cells(1, 3).Value = CStr(Var2)
    xlWs.Cells(1, 4).Value = CStr(Var3)
    xlWs.Cells(1, 5).Value = Var4
'   ...
'   ...  
End Sub

Private Sub Form_Activate()
    Set xlWb = GetObject("c:\test.xls")
    Set xlWs = xlWb.ActiveSheet
    xlWb.Application.Visible = True
    xlWb.Windows(1).Visible = True
    FillSheet
'  Here is the code, needed to save your
'  data in the sheet
    xlWs.Application.SaveWorkSpace("c:\test.xls")
End Sub

Private Sub Form_Unload()
    Set xlWb = Nothing
    Set xlWs = Nothing
End Sub


                   Pavel Tsekov
       email : paul_tsekov@yahoo.com
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.