Excel file becomes hidden

[IFRs]

Hi Folks -

I have a VBA program that I am having trouble with. It creates an Excel spreadsheet by copying an old one, then changes cell values, then saves and closes it. The problem is, when I open the worksheet in Excel it is HIDDEN. I have to go Window/UnHide and pick the file. What gives? Here is the code I use:

Set MasterObj = GetObject("M:\Master.xls&quot
MasterObj.SaveAs "J:\NewFile.xls"
MasterObj.Close
Set NewObj = GetObject("J:\NewFile.xls&quot
NewObj.Worksheets(1).Range("K13&quot.Value = 3
NewObj.Save
NewObj.Close

 

[dsi]

What application is the VBA code being stored? Assuming Excel, you can try something like this:

Sub Test()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim sSource As String
    Dim sDestination As String
    
    'Copy File
    sSource = "M:\Master.xls"
    sDestination = "J:\NewFile.xls"
    FileCopy sSource, sDestination
    
    'Update New File
    Set wb = Workbooks.Open(sDestination)
    Set ws = wb.Worksheets(1)
    ws.Range("K13").Value = 3
    wb.Close SaveChanges:=True
    
    'Clean Up
    Set ws = Nothing
    Set wb = Nothing
End Sub

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[IFRs]

dis - Thanks for your efforts!!

But...

My VB application is compiled to a standalone exe program. It solicits design parameters, allows adjustments and what-ifs, creates an AutoCAD dxf file, answers that get cut-and-pasted into master drawings AND creates a bill-of-material from a master Excel file. The copy part works, it puts data into the appropriate cells and when I quit the program, the Excel worksheet is there. Only, when I open the worksheet in Excel it is hidden !! Weird.

 

[dsi]

The code I posted will work in VB as well, as long as you add the Excel Object Library to your Project > References. If you do not want to include the object library, simply change the declarations to generic objects:

Dim wb As Object
Dim ws As Object

I have developed several applications that utilize Excel, but have never come across this situation before. I can only assume that the procedure you are using is somewhat flawed. If you can provide some additional details, I am sure that the problem can be fixed.
DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.