• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

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 .
1 Solution
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.
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 !!!!!!!

gingeAuthor Commented:
Also could you please go into a little more detail as I don't have much experience in this aspect of VB.
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

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.
' Free the object from memory.
      Set oXL = Nothing

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

' 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
'  Here is the code, needed to save your
'  data in the sheet
End Sub

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

                   Pavel Tsekov
       email : paul_tsekov@yahoo.com
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now