Jump to content



Photo

Excel from VB Question

excel 2007 vb6

  • Please log in to reply
1 reply to this topic

#1 crashbeta

crashbeta

    Resident One Post Wonder

  • Joined: 24-March 13

Posted 30 March 2013 - 00:44

HI all ,
I've been working on this for a while now and Obviously i'm probably missing something really easy ,
i'm trying to get data to goto excel from my VB 6 app , now the requirements i want my program to perform are
i do not want to see excel open i just want my app to write to the excel file in C:\Data\excel-data.xls
if i re-run my app i do not want it to tell me that it already Exists, Just write to the excel file and save it to the same location by same filename C:\Data\excel-data.xls
each day my app runs it puts data on separate rows by the day of the month " already have this done"
here is my simple VB code to write to excel, if anyone can help me out would be great !!
'Here is the Simple Program from Me
'To Send the Data from Vb To Microsoft Excel
Dim BangXl As Excel.Application

Private Sub Command1_Click()
BangXl.Workbooks.Add
BangXl.Range(Text1.Text).Value = Text2.Text
BangXl.Visible = True
End Sub

Private Sub Form_Load()
Set BangXl = CreateObject("excel.application")
End Sub
Private Sub Form_Unload(Cancel As Integer)
BangXl.Quit
End Sub


#2 CW-88

CW-88

    ||Modified||

  • Joined: 16-July 12

Posted 13 April 2013 - 20:30


Private Sub Command1_Click()
   Dim xlApp As Excel.Application
   Dim wrkbk As Workbook
   Dim wrksht As Worksheet
   Dim var As Variant

   Set xlApp = New Excel.Application

   Set wrkbk = xlApp.Workbooks.Open("PATH TO YOUR EXCEL FILE")
Wrkbk.visible = false

   Set wrksht = wrkbk.Worksheets("Sheet1") 'Specify your worksheet name
   var = wrksht.cells(a1).value

Var = 'vba userform control data
   wrkbk.Close

   xlApp.Quit

   Set wrksht = Nothing
   Set wrkbk = Nothing
   Set xlApp = Nothing



End Sub


Or you could write your data to a textile and then have the txtfile loaded into your excel document as a datasource and refresh the connection on workbook load which would be my preference to be honest.

I have a method for work I implemented which does just this for reporting purposes.



Click here to login or here to register to remove this ad, it's free!