Cannot open excel file during userform run

[metropol]

I have a userform with values which I want to pass to several sheets.

The userform is activated, and information is entered. Then this information should be passed to two other sheets. I have to open each sheet individually, store information and then save them.

When I run the macro, the Workbooks.Open does not execute/trigger an opening of the documents. the information is then stored in the sheet where the macro is triggered from

 

[ColinKJ]

Hi metropol,

If you wanted to pass the information to other sheets in the same workbook there shouldn't be a problem, so I assume you want to pass the data to sheets in other workbooks.

You'll either need to open the workbook(s) first before openning the Userform, or do a temp save of the data in the current WB, then open the other WB(s) and pass the data.

ColinKJ

 

[metropol]

Hi metropol,

If you wanted to pass the information to other sheets in the same workbook there shouldn't be a problem, so I assume you want to pass the data to sheets in other workbooks.

You'll either need to open the workbook(s) first before openning the Userform, or do a temp save of the data in the current WB, then open the other WB(s) and pass the data.

ColinKJ

True. I want to pass data to sheets in other workbooks. I cannot open the workbook before running the userform, so it has to be a temp save. Do you know a link to example code for this.......

 

[dualhcsniatpac]

Metropol, Here is the code for the program I am working on now. I have an excel file set up with JUST a form which then dumps info from the form into another Excel file. I think my code is way more involved than what you want but you might be able to manipulate it. Basically what the code does is create sheets and workbooks depending on month and year. This code is in its own Module and is called by the submit button.

I hope this helps because I have spent hours upon hours trying to get this code to work by learning from scratch.

' Call out variables
    Dim fname As String
    Dim SheetName As String

Sub CreateOpenFile()
Application.ScreenUpdating = False
        
    ' Formatted file name based on Month and Year
        fname = "High Volume Scrap " & Format(Date, "yyyy") & ".xls"
        SheetName = Format(Date, "mmmm")
    
    ' Sets the working directory
        ChDir ("C:\Test\")
    
    ' Test function to see if the file is there already
    
        If FileExists(fname) = False Then
        
        ' Creates File.
    
            Workbooks.Add
            
        ' Adds the first workbook
            Sheets.Add
            ActiveSheet.Name = "Volume and Buy Back " & Format(Date, "yyyy")
            Sheets.Add
            ActiveSheet.Name = Format(Date, "mmmm")
            
        ' Erase First 3 sheets of the Workbook
            
            Application.DisplayAlerts = False
            ActiveWorkbook.Worksheets("Sheet1").Delete
            ActiveWorkbook.Worksheets("Sheet2").Delete
            ActiveWorkbook.Worksheets("Sheet3").Delete
            Application.DisplayAlerts = True
                    
        ' Save the workbook.
    
            ActiveWorkbook.SaveAs Filename:="C:\Test\" & fname, FileFormat:=xlNormal, _
            Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
        
        ' Set the Headers for the Mass Spec Data (Sheet named Format(Date, "mmmm")
        
                Call MassSpecHeaders
        
        Else
        
        ' Open the file if it already exists.
            On Error Resume Next
            Workbooks.Open Filename:="C:\Test\" & fname
        
        End If
        
    ' Search the tabs for a current month tab.
    
        If SheetExists(SheetName) = False Then
        
            Sheets.Add
            ActiveSheet.Name = Format(Date, "mmmm")
            
            Call MassSpecHeaders   ' Inserts Headers for Mass Spec Data
        
        Else
        
            Sheets(SheetName).Select
        
        End If
        
End Sub
Function FileExists(fname As String) As Boolean
        
    ' Returns True if a file exists.
    
        FileExists = Dir(fname) <> ""
        
End Function
Function SheetExists(ByVal SheetName As String) As Boolean
    
    On Error Resume Next
    
    SheetExists = Not Sheets(SheetName) Is Nothing
    
    
End Function

 

[metropol]

Metropol, Here is the code for the program I am working on now. I have an excel file set up with JUST a form which then dumps info from the form into another Excel file. I think my code is way more involved than what you want but you might be able to manipulate it. Basically what the code does is create sheets and workbooks depending on month and year. This code is in its own Module and is called by the submit button.

I hope this helps because I have spent hours upon hours trying to get this code to work by learning from scratch.

' Call out variables
    Dim fname As String
    Dim SheetName As String

Sub CreateOpenFile()
Application.ScreenUpdating = False
        
    ' Formatted file name based on Month and Year
        fname = "High Volume Scrap " & Format(Date, "yyyy") & ".xls"
        SheetName = Format(Date, "mmmm")
    
    ' Sets the working directory
        ChDir ("C:\Test\")
    
    ' Test function to see if the file is there already
    
        If FileExists(fname) = False Then
        
        ' Creates File.
    
            Workbooks.Add
            
        ' Adds the first workbook
            Sheets.Add
            ActiveSheet.Name = "Volume and Buy Back " & Format(Date, "yyyy")
            Sheets.Add
            ActiveSheet.Name = Format(Date, "mmmm")
            
        ' Erase First 3 sheets of the Workbook
            
            Application.DisplayAlerts = False
            ActiveWorkbook.Worksheets("Sheet1").Delete
            ActiveWorkbook.Worksheets("Sheet2").Delete
            ActiveWorkbook.Worksheets("Sheet3").Delete
            Application.DisplayAlerts = True
                    
        ' Save the workbook.
    
            ActiveWorkbook.SaveAs Filename:="C:\Test\" & fname, FileFormat:=xlNormal, _
            Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
        
        ' Set the Headers for the Mass Spec Data (Sheet named Format(Date, "mmmm")
        
                Call MassSpecHeaders
        
        Else
        
        ' Open the file if it already exists.
            On Error Resume Next
            Workbooks.Open Filename:="C:\Test\" & fname
        
        End If
        
    ' Search the tabs for a current month tab.
    
        If SheetExists(SheetName) = False Then
        
            Sheets.Add
            ActiveSheet.Name = Format(Date, "mmmm")
            
            Call MassSpecHeaders   ' Inserts Headers for Mass Spec Data
        
        Else
        
            Sheets(SheetName).Select
        
        End If
        
End Sub
Function FileExists(fname As String) As Boolean
        
    ' Returns True if a file exists.
    
        FileExists = Dir(fname) <> ""
        
End Function
Function SheetExists(ByVal SheetName As String) As Boolean
    
    On Error Resume Next
    
    SheetExists = Not Sheets(SheetName) Is Nothing
    
    
End Function

Thanks dualhcsniatpac. a lot of good ideas here.

My struggle is from your code line:

' Open the file if it already exists.
            On Error Resume Next
            Workbooks.Open Filename:="C:\Test\" & fname

My userform is active, and i try to open workbooks thal already exist, but the line Workbooks.open is totally ignored.

 

[dualhcsniatpac]

That code is for when the file is closed. It says open the file at that location. The thing about my program is everytime I submit something the file is opened then IMMEDIATELY closed. So everytime this is called it is closed. As for the code being ignored I had to play around with it a lot. I would recommend using the step through to find out what is going on. Thats how I got it to work.

Glad I can help

 

[ColinKJ]

Hi metropol,

My suggestion about a temp save, was, while the Userform is still active, save the data to a spare sheet in the current workbook.

Then close the Userform, Open the required Workbook(s), transfer the saved date to the Workbook(s).

Alternativley, save the data in a temporary .txt file, close the Userform, Open the Workbooks and Open/Load the .txt file.

I believe that you can't open another Workbook while a Userform is active.

ColinKJ

 

[metropol]

I found a way around by opening the workbook, reading the values before the userform. Not very elegant but it's working.

Thanks anyway