Save data to Excel from Visual Basic

[wassup]

Hi all,

This my 1st time to get help at forum. So my problem is I have a form with some textbox inside and I want save the data that key in inside textbox to the Excel format that I create and the specified cell that I want it save into. Example, my Excel report's name is "Manual Visual Report", and one of the vb textbox is "Date". Lets say I want that textbox date save inside "Manual Visual Report", worksheet "LineA1", cell (5,4), so how to write the source code?

Thanks for the help.

*if can send me the example.

[danp129]

To clarify, you want to open an Excel document called "Manual Visual Report.xls", activate the sheet called "LineA1" and put in the text from your textbox into cell F5 ?

Am working on it now...

[danp129]

Private Sub Command1_Click()
    Dim XLapp As Excel.Application
    Dim wbkMVR As Excel.Workbook
    Dim wksLineA1 As Excel.Worksheet
    
    Dim sSaveAsFileName
    sSaveAsFileName = App.Path & "\" & "Manual Visual Report.xls"
    
    Set XLapp = CreateObject("Excel.Application")
    XLapp.Visible = True
    XLapp.Workbooks.Add
    Set wbkMVR = XLapp.ActiveWorkbook
    Set wksLineA1 = wbkMVR.Sheets(1)
    wksLineA1.Name = "LineA1"
    wksLineA1.Cells(5, 6).Value = Text1.Text

    XLapp.DisplayAlerts = False

    wbkMVR.SaveAs FileName:= _
        sSaveAsFileName, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    
    wbkMVR.Close SaveChanges:=False
    XLapp.Quit
    
    Set XLapp = Nothing
    Set wbkMVR = Nothing
    Set wksLineA1 = Nothing
    

End Sub

[wassup]

hi danp,

thanks for you help. i have to try the source code 1st. hopefully it works. ^_^

[wassup]

hi danp,

Sorry... its not the source code that I want. See picture below: [IMG]C:\Documents and Settings\chiang cs\Desktop[/IMG] . This is the excel format that I already create. And I want the textbox save into the cell that arrow show. Can you help me again? I am very appreciate of your help.

Thanks.

[wassup]

hi danp,

Sorry... its not the source code that I want. See picture below: [IMG]C:\Documents and Settings\chiang cs\Desktop[/IMG] . This is the excel format that I already create. And I want the textbox save into the cell that arrow show. Can you help me again? I am very appreciate of your help.

Thanks.

can you see the picture? or i send to you via email.

[danp129]

You can send it to my username @yahoo.com

[Lazareth]

Hi,

I am also wanting to save textbox to a sppecific cell and also read data from excel.

Can you please post solution if you fin one.

Cheers

[danp129]

Hi,

I am also wanting to save textbox to a sppecific cell and also read data from excel.

Can you please post solution if you fin one.

Cheers

Sure here's an example of the code above reading (msgbox reads the value!) the value of the cell it just wrote to...

Private Sub Command1_Click()
    Dim XLapp As Excel.Application
    Dim wbkMVR As Excel.Workbook
    Dim wksLineA1 As Excel.Worksheet
    
    Dim sSaveAsFileName
    sSaveAsFileName = App.Path & "\" & "Manual Visual Report.xls"
    
    Set XLapp = CreateObject("Excel.Application")
    XLapp.Visible = True
    XLapp.Workbooks.Add
    Set wbkMVR = XLapp.ActiveWorkbook
    Set wksLineA1 = wbkMVR.Sheets(1)
    wksLineA1.Name = "LineA1"
    wksLineA1.Cells(5, 6).Value = Text1.Text
    msgbox wksLineA1.Cells(5,6).value
    XLapp.DisplayAlerts = False

    wbkMVR.SaveAs FileName:= _
        sSaveAsFileName, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    
    wbkMVR.Close SaveChanges:=False
    XLapp.Quit
    
    Set XLapp = Nothing
    Set wbkMVR = Nothing
    Set wksLineA1 = Nothing
    

End Sub

[danp129]

can you see the picture? or i send to you via email.

Haven't got an e-mail I assume you already fixed your problem going off the example?

[wassup]

sorry danp, i m out of office this two days. i post to you now.

[wassup]

Here the example of my Worksheet format and my vb form.

[danp129]

This code assumes there is a template called "My Report Template.xls" in the path as the VB6 app (EXE path or Saved project..). It will open it, put the text in the cells specified in the code, then saves a copy of it as "Manual Visual Report.xls" in the same folder (it will overwrite old copies!). Your template will remain unchanged and ready to use again.

Private Sub Command1_Click()
    'Requires reference: Microsoft Excel (version) Object Library
    Dim XLapp As Excel.Application
    Dim wbkMVR As Excel.Workbook
    Dim wksLineA1 As Excel.Worksheet
    
    
    Dim sOpenFileName As String, sSaveAsFileName As String
    'Set path to template file here
    sOpenFileName = App.Path & "\" & "My Report Template.xls"
    'Set your path to save file here:
    sSaveAsFileName = App.Path & "\" & "Manual Visual Report.xls"
    
    'Create new instance of Excel
    Set XLapp = CreateObject("Excel.Application")
    
    'Make this instance visable to user
    XLapp.Visible = True
    
    'Using a template so don't add new workbook like this: XLapp.Workbooks.Add
    
    'Open template
    XLapp.Workbooks.Open (sOpenFileName)
    
    'Set reference to Active workbook (which should be the template we just opened)
    Set wbkMVR = XLapp.ActiveWorkbook
    'Set reference to first sheet in workbook
    Set wksLineA1 = wbkMVR.Sheets(1)
    'Rename worksheet
    wksLineA1.Name = "LineA1"
    'Copy text fields
    Stop 'And read this: I used E4 instead of F4 because it looks like E4 is beginning of your merged cells for that section
    wksLineA1.range("E4") = txtPro.Text
    wksLineA1.range("l4") = txtLine.Text
    wksLineA1.range("t4") = txtProDate.Text
    wksLineA1.range("ab4") = txtInspec.Text
    
    'Suppress Excel warnings
    XLapp.DisplayAlerts = False

    'Save workbook as whatever is in "sSaveAsFileName" variable
    wbkMVR.SaveAs FileName:= _
        sSaveAsFileName, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    
    'Close workbook
    wbkMVR.Close SaveChanges:=False
    
    'Close Excel
    XLapp.Quit
    
    'Remove refereneces
    Set XLapp = Nothing
    Set wbkMVR = Nothing
    Set wksLineA1 = Nothing


End Sub

[wassup]

Hi danp,

thanks and sorry for disturb you again. actually i have many form like frmLineA1, frmLineA2, frmLineA3 etc.... and also same with my Excel report, have sheet LineA1, LineA2, LineA3. so did you have any idea on how to run my program? my actual idea is the first time i click button on any form and it will use the template file save as "Manual Visual Report & date.caption", after that when i go to another form click button it will find is that have any same date report? if have it will add in data inside the report that save before without change the data before, if not it will create new report. thanks for your help.

[danp129]

This is quite a bit different than you first request, try lay out everything that's needed the first time you post and your issue can be resolved a little faster usually.

I'm pretty sure this is what you want.

Private Sub Command1_Click()
    'Requires reference: Microsoft Excel (version) Object Library
    Dim XLapp As New Excel.Application
    XLapp.Visible = True
    Dim wbkReport As Excel.Workbook
    Dim wbkTemplate As Excel.Workbook
    Dim wksReport As Excel.Worksheet
    Dim wksTemplate As Excel.Worksheet
    
    Dim sOpenFileName As String, sSaveAsFileName As String, iSheet As Integer
    
    'Set path to template file here
    sOpenFileName = App.Path & "\" & "My Report Template.xls"
    
    'Set your path to save file here:
    sSaveAsFileName = App.Path & "\" & "Manual Visual Report - " & Replace(Date, "/", "-") & ".xls"
        
    'Open template
    Set wbkTemplate = XLapp.Workbooks.Open(sOpenFileName)
    
    'Suppress Excel warnings
    XLapp.DisplayAlerts = False
    
    If Dir(sSaveAsFileName) <> "" Then
        'if a report from today exists, reset use it as the template
        Set wbkReport = XLapp.Workbooks.Open(sSaveAsFileName)
        Set wksTemplate = wbkTemplate.Sheets(1)
        wksTemplate.Copy after:=wbkReport.Sheets(wbkReport.Sheets.Count)
        Set wksReport = wbkReport.Sheets(wbkReport.Sheets.Count)
        wbkTemplate.Close SaveChanges:=False
    Else
        'This is the first report of the day
        Set wbkReport = wbkTemplate
        'Set reference to first sheet in workbook
        Set wksReport = wbkReport.Sheets(1)
        'Delete extra sheets from template
        If wbkReport.Sheets.Count > 1 Then
            'Start deleting extra sheets (only in this 'copy' of the template) 
            'if they exist, starting from last sheet
            For iSheet = wbkReport.Sheets.Count To 2 Step -1
                If wbkReport.Sheets(iSheet).Name = "Sheet" & iSheet Then wbkReport.Sheets(iSheet).Delete
            Next 'iSheet
        End If
    End If
    
    'Set report sheet name based on form name (duplicate names not allowed...)
    wksReport.Name = Mid(Me.Name, 4)
    
    'Copy text fields
    wksReport.range("E4") = txtPro.Text
    wksReport.range("l4") = txtLine.Text
    wksReport.range("t4") = txtProDate.Text
    wksReport.range("ab4") = txtInspec.Text
    
    'Save workbook as whatever is in "sSaveAsFileName" variable
    wbkReport.SaveAs FileName:= _
        sSaveAsFileName, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    
    'Close workbook
    wbkReport.Close SaveChanges:=False
    
    'Allow Excel warnings
    XLapp.DisplayAlerts = True
    
    'Remove references to sheets/workbooks before quitting (was hanging a second on wksreport if Excel was closed already)
    Set wksReport = Nothing
    Set wbkReport = Nothing
    Set wksTemplate = Nothing
    Set wbkTemplate = Nothing
    
    'Close Excel
    XLapp.Quit
    
    'Remove final reference
    Set XLapp = Nothing
End Sub