problem when save to an excel spreadhseet in vb

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
pele4483Asked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
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
 
Ryan ChongCommented:
try use

xlTmp.SaveAs yourFileName


instead?
0
 
Ryan ChongCommented:
sorry, should be as:

xltmp.ActiveWorkbook.SaveAs yourFileName
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Ryan ChongCommented:
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
 
pele4483Author Commented:
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
 
pele4483Author Commented:
it looks like xltmp.ActiveWorkbook.SaveAs yourFileName works without the resume fiel problem but i need to save automatically without being prompted.
0
 
Rory ArchibaldCommented:
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
 
pele4483Author Commented:
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
 
Rory ArchibaldCommented:
The basic syntax is something like:
.cells(row, 7).Hyperlinks.Add Anchor:=.cells(row, 7), Address:=SolicitationHTMLFileName, TextToDisplay:=SolicitationHTMLFileName

HTH
Rory
0
 
pele4483Author Commented:
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
 
Rory ArchibaldConnect With a Mentor Commented:
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
 
pele4483Author Commented:
very good thanks for all your help
0
 
DhaestCommented:
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
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.

All Courses

From novice to tech pro — start learning today.