Solved

problem when save to an excel spreadhseet in vb

Posted on 2007-04-02
14
307 Views
Last Modified: 2013-12-25
i am trying to write to an excel spreadsheet from my vb app.  I can write to the cells but when i do the close in the code it asks me to save the file, which works fine if I answer . I want to save and close the file automatically.  I added the save code but now it comes up with "A file named RESUME.XLW already exists, do you want to replace it" . how do i get around this problem.

My code is as follows

Public Sub Make_Excel_Bid_Sheet()
'Set objExcel = CreateObject("Excel.Application")
'objExcel.Visible = True
'objExcel.Workbooks.Open "c:\xyz4.xls"

'Set objWorkbook = objExcel.Workbooks.Add
'Set objWorksheet = objWorkbook.Sheets("Sheet1")

'Dim xlSht As objExcel.Worksheet
'Set xlSht = objExcel.Sheets("Sheet1")

'objWorksheet.cells(1, 1) = "Script Center"

'Set objRange = objExcel.Range("A1")
'Set objLink = objWorksheet.Hyperlinks.Add _
 '   (objRange, "http://www.microsoft.com/technet/scriptcenter")

'Exit Sub



Dim xlTmp As excel.Application
Set xlTmp = New excel.Application
xlTmp.Workbooks.Open "c:\xyz4.xls"
Dim xlSht As excel.Worksheet
Set xlSht = xlTmp.Sheets("Sheet1")

Dim row As Long

With xlSht
row = .cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).row + 1

 .cells(row, 1) = Me.QuotationID
 .cells(row, 2) = Me.SolicitationDescription
 .cells(row, 3) = YourRequestNoFull
 .cells(row, 4) = SolicitationNumber ' PR DOC NUMBER
 .cells(row, 5) = BidDueDate
 .cells(row, 6) = IssueDate
.cells(row, 7) = SolicitationHTMLFileName
  .cells(row, 8) = Me.Form![NSNTable Subform10]!NSNNUmber
  .cells(row, 9) = Me.Form![NSNTable Subform10]!cmbPartRev.Column(1)
  .cells(row, 10) = Me.Form![NSNTable Subform10]!SUBPart
 .cells(row, 11) = Me.Form![NSNTable Subform10]!Quantity
 .cells(row, 12) = SolicitationFName

End With
'xlTmp.Save    TRIED THIS BUT GET PROBLEM WITH RESUME FILE

xlTmp.Quit
  Set xlTmp = Nothing
0
Comment
Question by:pele4483
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 18836239
try use

xlTmp.SaveAs yourFileName


instead?
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 18836263
sorry, should be as:

xltmp.ActiveWorkbook.SaveAs yourFileName
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 18836275
or you can try like this...

....
 Dim xlTmp As Excel.Application, wb As Excel.Workbook
    Set xlTmp = New Excel.Application
    Set wb = xlTmp.Workbooks.Open("c:\xyz4.xls")
    Dim xlSht As Excel.Worksheet
    Set xlSht = wb.Worksheets("Sheet1")
   
    Dim row As Long
   
    With xlSht
        row = .cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).row + 1
       
        .cells(row, 1) = Me.QuotationID
        .cells(row, 2) = Me.SolicitationDescription
        .cells(row, 3) = YourRequestNoFull
        .cells(row, 4) = SolicitationNumber ' PR DOC NUMBER
        .cells(row, 5) = BidDueDate
        .cells(row, 6) = IssueDate
        .cells(row, 7) = SolicitationHTMLFileName
        .cells(row, 8) = Me.Form![NSNTable Subform10]!NSNNUmber
        .cells(row, 9) = Me.Form![NSNTable Subform10]!cmbPartRev.Column(1)
        .cells(row, 10) = Me.Form![NSNTable Subform10]!SUBPart
        .cells(row, 11) = Me.Form![NSNTable Subform10]!Quantity
        .cells(row, 12) = SolicitationFName
    End With
    'xlTmp.Save    TRIED THIS BUT GET PROBLEM WITH RESUME FILE
    wb.SaveAs YourFileName
    wb.Close
    Set xlSht = Nothing
    Set wb = Nothing
    xlTmp.Quit
    Set xlTmp = Nothing
...


hope this helps
0
 

Author Comment

by:pele4483
ID: 18836877
the saveas is causing   "object doesn't support the method" when it tries to execute.  I can't belive this is that hard.  I need to keep opening the spreadhseet and appending to it. so i don;t know if sdavesas would work anyways.
0
 

Author Comment

by:pele4483
ID: 18836926
it looks like xltmp.ActiveWorkbook.SaveAs yourFileName works without the resume fiel problem but i need to save automatically without being prompted.
0
 
LVL 49

Accepted Solution

by:
Ryan Chong earned 25 total points
ID: 18837035
try this:

    Dim xlTmp As Excel.Application, wb As Excel.Workbook
    Set xlTmp = New Excel.Application
    Set wb = xlTmp.Workbooks.Open("c:\xyz4.xls")
    Dim xlSht As Excel.Worksheet
    Set xlSht = wb.Worksheets("Sheet1")
    YourFileName = "C:\test.xls"
    Dim row As Long
    Dim r As Range
    With xlSht
        Set r = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
        If Not r Is Nothing Then
            row = r.row + 1
            .Cells(row, 1) = Me.QuotationID
            .Cells(row, 2) = Me.SolicitationDescription
            .Cells(row, 3) = YourRequestNoFull
            .Cells(row, 4) = SolicitationNumber ' PR DOC NUMBER
            .Cells(row, 5) = BidDueDate
            .Cells(row, 6) = IssueDate
            .Cells(row, 7) = SolicitationHTMLFileName
            .Cells(row, 8) = Me.Form![NSNTable Subform10]!NSNNUmber
            .Cells(row, 9) = Me.Form![NSNTable Subform10]!cmbPartRev.Column(1)
            .Cells(row, 10) = Me.Form![NSNTable Subform10]!SUBPart
            .Cells(row, 11) = Me.Form![NSNTable Subform10]!Quantity
            .Cells(row, 12) = SolicitationFName
        End If
    End With
    'xlTmp.Save    TRIED THIS BUT GET PROBLEM WITH RESUME FILE
    If Dir$(YourFileName) <> "" Then Kill YourFileName
    wb.SaveAs YourFileName
    wb.Close
    Set xlSht = Nothing
    Set wb = Nothing
    xlTmp.Quit
    Set xlTmp = Nothing

?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18837117
If you are just amending and saving the workbook, then you can use:

Dim xlTmp As Excel.Application, wb As Excel.Workbook
    Set xlTmp = New Excel.Application
    Set wb = xlTmp.Workbooks.Open("c:\xyz4.xls")
    Dim xlSht As Excel.Worksheet
    Set xlSht = wb.Worksheets("Sheet1")
   
    Dim row As Long
   
    With xlSht
        row = .cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).row + 1
       
        .cells(row, 1) = Me.QuotationID
        .cells(row, 2) = Me.SolicitationDescription
        .cells(row, 3) = YourRequestNoFull
        .cells(row, 4) = SolicitationNumber ' PR DOC NUMBER
        .cells(row, 5) = BidDueDate
        .cells(row, 6) = IssueDate
        .cells(row, 7) = SolicitationHTMLFileName
        .cells(row, 8) = Me.Form![NSNTable Subform10]!NSNNUmber
        .cells(row, 9) = Me.Form![NSNTable Subform10]!cmbPartRev.Column(1)
        .cells(row, 10) = Me.Form![NSNTable Subform10]!SUBPart
        .cells(row, 11) = Me.Form![NSNTable Subform10]!Quantity
        .cells(row, 12) = SolicitationFName
    End With
    wb.Save
    Set xlSht = Nothing
    wb.Close false
    Set wb = Nothing
    xlTmp.Quit
    Set xlTmp = Nothing

Regards,
Rory
0
 

Author Comment

by:pele4483
ID: 18837806
this last method of appending works great! Now there is one more issue.  My 7th and 12th column needs to be a hyperlink field so that when i put the cursor over the field I can click and go to the hyperlink.  I tried to see if i could set the whole column to a hyperlink and that isn't an option, so i need to save the information as a hyperlink.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18841865
The basic syntax is something like:
.cells(row, 7).Hyperlinks.Add Anchor:=.cells(row, 7), Address:=SolicitationHTMLFileName, TextToDisplay:=SolicitationHTMLFileName

HTH
Rory
0
 

Author Comment

by:pele4483
ID: 18842334
well i got the correct syntax for the hyperlink add. But it works when i go into my routine the first time then I keep getting error 1004 "method add of hyperlinks failed" on subsequent calls to the routine.. I wrote a test program to add a new hyoerlink everytime i call the subroutine but i switch the row each time. You woudl think this wouldn't be so difficult!

Dim xlTmp As excel.Application, wb As excel.Workbook
    Set xlTmp = New excel.Application
    Set wb = xlTmp.Workbooks.Open("c:\xyz.xls")
    Dim xlSht As excel.Worksheet
    Set xlSht = wb.Worksheets("Sheet1")
Dim row As Long
row = 3

With xlSht
row = .cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).row + 1

.Hyperlinks.Add _
        Anchor:=cells(row, 13), _
       Address:="http://example.microsoft.com", _
        ScreenTip:="Microsoft Web Site", _
        TextToDisplay:="test"
End With
 
     wb.Save
    Set xlSht = Nothing
    wb.Close False
    Set wb = Nothing
    xlTmp.Quit
    Set xlTmp = Nothing
  Set xlTmp = Nothing
Exit Sub
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 25 total points
ID: 18842378
That works fine for me as long as you change:
.Hyperlinks.Add _
        Anchor:=cells(row, 13), _
       Address:="http://example.microsoft.com", _
        ScreenTip:="Microsoft Web Site", _
        TextToDisplay:="test"

to this:
.Hyperlinks.Add _
        Anchor:=.cells(row, 13), _
       Address:="http://example.microsoft.com", _
        ScreenTip:="Microsoft Web Site", _
        TextToDisplay:="test"

Note the period before the Cells keyword.
Regards,
Rory
0
 

Author Comment

by:pele4483
ID: 18842827
very good thanks for all your help
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 21573421
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup Zone:
Split points between ryancys and rorya

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

Dhaest Experts Exchange Cleanup Volunteer
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now