Solved

problem when save to an excel spreadhseet in vb

Posted on 2007-04-02
14
303 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

758 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

18 Experts available now in Live!

Get 1:1 Help Now