?
Solved

problem when save to an excel spreadhseet in vb

Posted on 2007-04-02
14
Medium Priority
?
331 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
13 Comments
 
LVL 54

Expert Comment

by:Ryan Chong
ID: 18836239
try use

xlTmp.SaveAs yourFileName


instead?
0
 
LVL 54

Expert Comment

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

xltmp.ActiveWorkbook.SaveAs yourFileName
0
 
LVL 54

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 54

Accepted Solution

by:
Ryan Chong earned 100 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
 
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 100 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

850 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