CODEWITHSUNDEEP
vbaexcelsynchronizationsave
Nicolas
Nicolas

Reputation: 1171

Save/load user preference configuration file

I would like to add SAVE/OPEN functionality to a little Excel program I wrote. To use the program, users have to fill in data and adjust settings across several sheets. I would like to save this into a configuration file to be able able to load it later.

How should I build this SAVE/OPEN functionality?

My idea was to group the entered data and configuration on a single sheet (let's name it Entries) through simple "links" (=Sheet1!A1 for example). This sheet would be exported.

I am saving the entries into a new .xls workbook this way:

ActiveWorkbook.Sheets("Entries").Columns("A:B").copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False 'Paste only values

But the challenge comes when loading the data...

There are several examples on the net (see here and there) that show how to do, but always with the same problem. That is: if I just copy the data from the saved workbook (values only) to the Entries sheet, all my "links" will be erased. Is there a way to synchronize the data?

Or is the only solution to manually enter all the cell values in VBA and doing hundreds of range copies, from the exported workbook directly to the cells used by the user?

Upvotes: 0

Views: 2134

Answers (2)

Nicolas
Nicolas

Reputation: 1171

Here is the solution found. Thanks a lot for the help!

Saving the entries...

Set CurSheet = ActiveWorkbook.Sheets("Entries")
    maxEntries = 150
    CurSheet.copy 'a new workbook is created

    Set wbDest = ActiveWorkbook
    wbDest.Sheets(1).Range("A1:B" & maxEntries).Value = CurSheet.Range("A1:B" & maxEntries).Value
    wbDest.Sheets(1).Range("C1:C" & maxEntries).Value = CurSheet.Range("B1:B" & maxEntries).Formula

    For i = 1 To maxEntries

        'Removes the leading "=" from the formula
        tempCell = ActiveWorkbook.Sheets(1).Range("C" & i).Formula
        If Len(tempCell) > 1 Then
            wbDest.Sheets(1).Range("C" & i).Value = Right(tempCell, Len(tempCell) - 1)
        End If

        'For empty cells
        If wbDest.Sheets(1).Range("B" & i).Value = 0 Then
            wbDest.Sheets(1).Range("B" & i).Value = ""
        End If

    Next i

... and loading them.

fullFileName = Application.GetOpenFilename("Excel files (*.xls),*.xls", _
        1, "Projekt öffnen", , False)

    Workbooks.Open fullFileName
    Set wbSaved = ActiveWorkbook      

    'Data copy
    maxEntries = 150

    For i = 4 To maxEntries

        If Not wbSaved.Sheets(1).Range("C" & i) = "" Then 'Skip the empty lines

            'Parsing
            c = wbSaved.Sheets(1).Range("C" & i).Value
            l = Len(c)
            p = InStr(1, c, "!", vbTextCompare) 'position of the !, that separates the sheet name from the cell number
            cDestSheet = Mid(c, 1, p - 1)
            cDestCell = Mid(c, p + 1, -(p - l))

            'Copy
            wbMain.Sheets(cDestSheet).Range(cDestCell).Value = wbSaved.Sheets(1).Range("B" & i).Value
        End If
    Next i

    wbSaved.Close False

Upvotes: 2

Gaffi
Gaffi

Reputation: 4367

I have an idea that may not be a complete answer yet, but if this sounds good, I'll elaborate some... Using your example:

ActiveWorkbook.Sheets("Entries").Columns("A:B").copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False 'Paste only values

I would change to this (incomplete and untested code, but you should get the idea):

Dim CurSheet As WorkSheet

Set CurSheet = ActiveWorkbook.Sheets("Entries")

With Workbooks.Add
    Sheets(1).Range("A:B").value = CurSheet.Range("A:B").value
    Sheets(2).Range("A:B").value = "'" & CurSheet.Range("A:B").formula
End With

What I'm doing here is taking the values as you already have and plugging them into the first sheet of the new workbook (see first line within With block), then also taking the text value of the formula and putting it into the second sheet, same position (see second line). In this way you can keep the values and also see where they came from. However, this is not really synchronizing like you ask for, since you would still have to manipulate the data in sheet 2 of the new book to do anything meaningful with those links.

Does this help/get you started in the right direction?

Upvotes: 1

Related Questions