Is it possible to implement an export/import feature for the unlocked cells in my Excel file? (Through VBA?)

[Rnkhch]

Hello,

I have made an Excel application, with protected worksheets and workbook, that I have updated and improved over the last several months, and it continues to evolve as I add more features and code as the need arises. One issue though is that each time I make an updated version, I have to manually copy/paste the data from all unlocked segments into the new file.

I'm hoping that it would be possible to implement an export/import feature in my application such that I would press an export button that would save the unlocked data in a file (perhaps through a "save as" window", and then I would press an import button and select the saved data (perhaps through an "open" window) and have all the data be populated in the correct cells.

If possible, it would be fabulous, and I would highly appreciate the help from the VBA experts here

 

[Alex Blakenburg]

That could be really slow. How many sheets and how many input cells are we talking about ?
Do you need an export or can you just open the old copy of the workbook and use that to populate the new workbook ?
If you move rows and / or column the data won't line up, what happens then ?

 

[Rnkhch]

Thanks. I got very excited to see your reply as now I hope doing this can be feasible. Here are the responses to your questions in order:

There are about 29,000 (29 thousand) unlocked cells where the bulk is one in sheet, and about 100 or so of them in another sheet.

Opening an old copy would also work if that makes things simpler in terms of coding (though export is fancier so the users may like it better)

My sheets are locked, so it won't be possible to move cells, rows or columns.

Please let me know what you think

 

[Alex Blakenburg]

My sheets are locked, so it won't be possible to move cells, rows or columns.

I wasn't worried about the user adding rows and columns I was worried about the new template having rows and columns in a different place.

each time I make an updated version

 

[Alex Blakenburg]

Here is a feasibility test for you.

Change the sheet name of wsCurrentMain to your sheet with the 29k input records and see how long it takes to run.
It will create a new workbook with all the unlocked cells and their values. (Export function)

I suspect it will take too long, but see how you go.

Sub ExportData()

    Dim wbCurrent As Workbook, wbExport As Workbook
    Dim wsCurrentMain As Worksheet, wsExportMain As Worksheet
    Dim rngCurrent As Range
    Dim cellUnlock As Range
    Dim arrUnlock As Variant
    Dim i As Long
    Dim FirstCell As Range
    Dim WBPrefix As String
    Dim CurrCell As Range
    
    Dim startTime As Double
    startTime = Timer
    
    Set wbCurrent = ThisWorkbook
    Set wsCurrentMain = wbCurrent.Worksheets("Main Data")       ' <--- Change this to your sheet with 29k unlocked cells
    Set rngCurrent = wsCurrentMain.UsedRange

    ReDim arrUnlock(1 To rngCurrent.Cells.Count, 1 To 1)
        
    With Application.FindFormat
        .Clear
        .Locked = False
    End With
    
    With rngCurrent
        Set FirstCell = .Cells.Find(What:="", After:=.Cells(1, 1), _
                            LookIn:=xlFormulas, LookAt:=xlPart, _
                            SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False, SearchFormat:=True)
    End With

    If Not FirstCell Is Nothing Then
        Set CurrCell = FirstCell

        Do
            i = i + 1
            arrUnlock(i, 1) = CurrCell.Address(0, 0, 1, 1) & "~" & CurrCell.Formula
            WBPrefix = Split(arrUnlock(i, 1), "]")(0) & "]"
            arrUnlock(i, 1) = Replace(arrUnlock(i, 1), WBPrefix, "'")

            Set CurrCell = rngCurrent.Cells.Find(What:="", After:=CurrCell, _
                LookIn:=xlFormulas, LookAt:=xlPart, _
                SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=True)
        Loop Until CurrCell.Address = FirstCell.Address

        Set wbExport = Workbooks.Add(xlWBATWorksheet)
        Set wsExportMain = ActiveSheet
        
        With wsExportMain
            .Range("A1").Resize(i).Value = arrUnlock
            .Columns(1).AutoFit
        End With

    End If
    
    Application.FindFormat.Clear
    
    MsgBox "Duration in seconds: " & Timer - startTime
End Sub

 

[Rnkhch]

I just tested your code, but when I click the run button, it gives me this error: runtime error 9, script out of range. Any reason why this may be happening? Or am I supposed to run it in a different way?

I may be writing this incorrectly, but I substituted the three occurrences of "wsCurrentMain" with "PlatesInput".

Actually, the original name of my sheet was Plates-Input, but since it was having problem with the hyphen and I didn't know what to do about the hyphen, I removed it and now it is called PlatesInput.

Also in response to your earlier question, all the positions for my unlocked cells remain constant through newer version of the application.

Thank you.

 

[Alex Blakenburg]

The only thing you needed to change was this line:

    Set wsCurrentMain = wbCurrent.Worksheets("PlatesInput")

Let me know how you go once you have tried that.

 

[Rnkhch]

Oh I see, my bad. Now it seems to be working. I've been trying for the last hour or so, and I filled about a third of my big sheet with numbers, and when I click on the run button, it says 0.03125 seconds in a pop-up message. So does this mean that the overall export process should be pretty fast?

Now I did not see any new files (workbooks) generated, unless they were generated in another location?

 

[Alex Blakenburg]

I don't want to comment on time until you get an output.
This line should have created a new Open Workbook with just 1 sheet in it and put the Sheet name-Cell Address & Cell Content of every unlocked cell on that sheet in 1 column.

        Set wbExport = Workbooks.Add(xlWBATWorksheet)

Is that not happening ?
If not, can you F8 through the code and see what happens when that line runs ?

How many cells you populate shouldn't matter, since it should output all the Unlocked cells whether they have content or not.

 

[Rnkhch]

Thank you. I checked the code with F8. I found out that the code gets to:

          If Not FirstCell Is Nothing Then

And then it jumps to:

          End If

So none of the lines in between (where "Set wbExport = Workbooks.Add(xlWBATWorksheet)" is) are ever highlighted. If I need to change anything in the syntax, please let me know.

Thanks.