dcsimg
Results 1 to 12 of 12

Thread: Attaching Excel database with the setup Packaging

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2019
    Posts
    8

    Post Attaching Excel database with the setup Packaging

    I am creating a program that is simply an user-form displaying data received from an excel file (as a simple database) and also may need to add data back to the excel file. I've figured out how to do the same by referencing the local path of the file on my computer and can also use the deployment wizard to create the setup file. However, I am having trouble with attaching (bundling) the excel database along with the setup files as I want to share this program with other people (I have also used late binding for the same). I also want the presence of the excel file in the setup bundle to be as discreet as possible, best if the users cannot figure out that the database is, in fact, an excel file. Please help me figure this out.

  2. #2
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    5,663

    Re: Attaching Excel database with the setup Packaging

    Hi Swop_K,

    First, welcome to VBForums.

    Next, I distribute many Excel files with my primary application, but I don't do it quite as you're suggesting. My primary application is quite large, but I still prefer to keep it portable (i.e., not requiring any installation). I also distribute it as a single executable.

    Now, you've actually raised several interesting questions:

    • Keeping things late-bound, as this will detach you from any particular version of Excel, but still require that some version be available for automation.
    • Being "discreet". This invokes ideas of possibly placing things in the ProgramData or possibly Users\[user]\AppData area.
    • Possibly distributing your empty Excel file in the resources area of your application.

    I'll attempt to address the last of those first. Down at post #9 of this tutorial, I talk about packing a file into the resources area of a VB6 project. In that post, I'm talking about an OCX file, but the exact same ideas could be applied to an XLS or XLSX (or even XLT or XLTX) file.

    Now, before I leave my discussion of using the resources area, when packing large files in there, when they come back out, they're rounded-up to the nearest four-byte size. For most files, this doesn't matter. However, with the new XLSX (or XLTX or XLMX, etc) files, it does matter. When they're opened, some kind of a checksum is performed, and the potential extra bytes give Excel fits. I've got code that fixes that (for when extracting from resources). If you ask for it (after deciding to take this approach), I'll post it.

    ----

    Next, being "discreet". If you choose to use either the ProgramData or Users\[user]\AppData area, please be sure and use the appropriate API call to get these areas. 99% of the time, they're in the same place, but the user does have the option of moving them. So I wouldn't recommend hard-coding this. There are many examples of these API calls in these forums, but, if you ask, I'll help more with this

    ----

    Next, keeping things late-bound. It seems that you've got that all worked out. It's not terribly difficult. However, I've been doing that for years and have a great deal of code around doing it. I hope all of that is going well.

    ----

    Best of Luck,
    Elroy
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  3. #3

    Thread Starter
    New Member
    Join Date
    Jul 2019
    Posts
    8

    Re: Attaching Excel database with the setup Packaging

    Hi! Thank you for your reply.
    Few questions:
    When you say you distribute excel file in a single exe file, do you do it by adding the file in the Resources Editor like you've described in one of your posts?

  4. #4

    Thread Starter
    New Member
    Join Date
    Jul 2019
    Posts
    8

    Re: Attaching Excel database with the setup Packaging

    After adding my file in the resource editor, how do I indicate in my code that the file I am working with is the same file as in the RE? I am guessing it goes something like "LoadResData.." but can't figure out how to use it in place of this code that I am currently using for local file path:
    Code:
    Set oXLBook = oXLApp.Workbooks.Open("F:\OLS Tools\database1.xlsx")

  5. #5
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    5,663

    Re: Attaching Excel database with the setup Packaging

    Continuing from my question in the forum, how do I work with the excel file (now loaded in the resources area) in the project code? Any documentation and/or sample would be really helpful. Also, could be let me know about your preferred workflow when you "attach" your excel files in the single EXE file?
    Ok, the following is a self-contained module that has the code (peeled out of my large project) that I use for doing this:

    Code:
    
    Option Explicit
    '
    Private Declare Function ArrPtr Lib "msvbvm60" Alias "VarPtr" (a() As Any) As Long
    Private Declare Function GetMem4 Lib "msvbvm60" (ByRef Source As Any, ByRef Dest As Any) As Long ' Always ignore the returned value, it's useless.
    Private Declare Function GetMem2 Lib "msvbvm60" (ByRef Source As Any, ByRef Dest As Any) As Long ' Always ignore the returned value, it's useless.
    '
    
    Public Sub RetrieveResourceFileAndSaveToDisk(sFilePath As String, sFileName As String, sResourceType As String, Optional bOverwriteIfExists As Boolean = True)
        ' Loads the specified file from the .RES (or .EXE) and then saves it to disk.
        '
        Dim bb() As Byte
        Dim sSaveSpec As String
        '
        ' Build filespec.
        If Right$(sFilePath, 1) = "\" Then
            sSaveSpec = sFilePath & sFileName
        Else
            sSaveSpec = sFilePath & "\" & sFileName
        End If
        '
        ' Check if the file exists.  Get out if it does and we don't want to overwrite.
        ' It saves time and memory to go ahead and do this first.
        If bFileExists(sSaveSpec) Then
            If Not bOverwriteIfExists Then Exit Sub
            Kill sSaveSpec
        End If
        '
        ' Actually retrieve and save the file.
        SaveResourceFileToDisk LoadResourceFileFromName(sFileName, sResourceType), sSaveSpec, bOverwriteIfExists
    End Sub
    
    Public Function LoadResourceFileFromName(sFileName As String, sResourceType As String) As Byte()
        ' Loads the specified data file resource from the current project's resource (.RES) file, actually the .EXE once compiled.
        '   sFileName            Specifies the unique name of the data file resource.
        '   sResourceType        Optional. Specifies the "type" of data file being returned.  The Visual Basic default for data files is "CUSTOM".
        '
        ' Will error if not found.  Note that CASE doesn't matter in the LoadResData call.
        ' However, spaces are not allowed in resource names, so they are removed.
        LoadResourceFileFromName = LoadResData(Replace$(sFileName, " ", vbNullString), sResourceType) ' Returns a Byte array.
    End Function
    
    Public Sub SaveResourceFileToDisk(DataArray() As Byte, sSaveSpec As String, Optional bOverwriteIfExists As Boolean = True)
        ' This function takes the specified data file in the form of a BYTE array and saves it out to the specified file.
        '
        ' sSaveSpec            Specifies the full path of the file to save out to.
        ' DataArray            Specifies the BYTE array that represents the data file to save out.
        ' bOverwriteIfExists   Optional. If set to TRUE and the file specified in the "sSavePath" parameter already
        '                      exists, the existing file will be overwritten with the new one.  If set to FALSE, the
        '                      existing file is left alone and the specified data file is not written out.
        '
        ' Return FALSE on error, TRUE if successful.
        Dim FileNum As Integer
        Dim sFileName As String
        Dim i As Long
        Dim s As String
        '
        If Not IsByteDimmed(DataArray) Then Exit Sub
        '
        ' We've got to take special care with Office XML type files.
        ' So, we search for the ZIP EOF marker, and trim any extra bytes.
        ' This only applies to files that are larger than 65520.
        '
        ' Also, as Olaf Schmidt pointed out: "Please consider, that the last two bytes of the Zip-EndHeader will describe the length
        '           of an optional String-Comment (which would then directly follow that Zip-EndHeader).  So, just going 22Bytes (or 25Bytes)
        '           back to find the EndHeader will not be sufficient, when the Zip contains such a Comment (which could be 64KB long as it seems)."
        ' The following is his recommended fix:
        '  Debug.Print "ByteArray-Size:"; UBound(b) + 1
        '  For i = UBound(b) - 3 To 0 Step -1
        '    If b(i) = 80 And b(i + 1) = 75 And b(i + 2) = 5 And b(i + 3) = 6 Then
        '      Debug.Print "ExpectedZipLen:"; i + 22 + b(i + 20) + 256& * b(i + 21)
        '      Exit For
        '    End If
        '  Next
        '
        If UBound(DataArray) > 65519 Then   ' Zero based.
            s = UCase$(Right$(sSaveSpec, 5))
            Select Case s
            Case ".XLSX", ".XLTX", ".XLSM", ".XLTM", ".DOCX", ".DOTX", ".DOCM", ".DOTM"
                Select Case True
                Case DataArray(UBound(DataArray) - 21) = CByte(&H50) And _
                     DataArray(UBound(DataArray) - 20) = CByte(&H4B) And _
                     DataArray(UBound(DataArray) - 19) = CByte(&H5) And _
                     DataArray(UBound(DataArray) - 18) = CByte(&H6)             ' In this case, we're good to go.
                        '
                Case DataArray(UBound(DataArray) - 22) = CByte(&H50) And _
                     DataArray(UBound(DataArray) - 21) = CByte(&H4B) And _
                     DataArray(UBound(DataArray) - 20) = CByte(&H5) And _
                     DataArray(UBound(DataArray) - 19) = CByte(&H6)             ' Crop a byte.
                        ReDim Preserve DataArray(0 To UBound(DataArray) - 1)
                        '
                Case DataArray(UBound(DataArray) - 23) = CByte(&H50) And _
                     DataArray(UBound(DataArray) - 22) = CByte(&H4B) And _
                     DataArray(UBound(DataArray) - 21) = CByte(&H5) And _
                     DataArray(UBound(DataArray) - 20) = CByte(&H6)             ' Crop two bytes.
                        ReDim Preserve DataArray(0 To UBound(DataArray) - 2)
                        '
                Case DataArray(UBound(DataArray) - 24) = CByte(&H50) And _
                     DataArray(UBound(DataArray) - 23) = CByte(&H4B) And _
                     DataArray(UBound(DataArray) - 22) = CByte(&H5) And _
                     DataArray(UBound(DataArray) - 21) = CByte(&H6)             ' Crop three bytes.
                        ReDim Preserve DataArray(0 To UBound(DataArray) - 3)
                        '
                Case Else
                        ' Just leave it alone and hope for the best.
                End Select
            End Select
        End If
        '
        ' Check if the file exists
        If bFileExists(sSaveSpec) Then
            If Not bOverwriteIfExists Then Exit Sub
            Kill sSaveSpec
        End If
        '
        sFileName = Mid$(sSaveSpec, InStrRev(sSaveSpec, "\") + 1)
        '
        ' Save the information to file.
        FileNum = FreeFile
        Open sSaveSpec For Binary As #FileNum
        On Error Resume Next
            Put #FileNum, 1, DataArray()
        On Error GoTo 0
        Close #FileNum
    End Sub
    
    
    
    
    ' Other procedures used to get the above done.
    
    
    Private Function bFileExists(fle As String) As Boolean
        On Error GoTo FileExistsError
        ' If no error then something existed.
        bFileExists = (GetAttr(fle) And vbDirectory) = 0&
        Exit Function
    FileExistsError:
        bFileExists = False
    End Function
    
    Public Function PtrAdd(ByVal Ptr As Long, ByVal iOffset As Long) As Long
        ' For adding (or subtracting) a small number from a pointer.
        ' Use PtrAddEx for adding (or subtracting) large numbers from a pointer.
        PtrAdd = (Ptr Xor &H80000000) + iOffset Xor &H80000000
    End Function
    
    Public Function IsByteDimmed(TheArray() As Byte, Optional FailOnZeroNegOne As Boolean = True) As Boolean
        IsByteDimmed = IsPtrDimmed(ArrPtr(TheArray), FailOnZeroNegOne)
    End Function
    
    Public Function IsPtrDimmed(pArray As Long, Optional FailOnZeroNegOne As Boolean = True) As Boolean
        ' Works with all array types (static or dynamic), including UDTs, but with these exceptions:
        '       Excludes BSTR String arrays.  Use IsStrDimmed instead.
        '       Excludes Variants CONTAINING arrays.
        '    Variant arrays (not an array containing a variant) will work fine.
        '    Also fixed-length String arrays (static or dynamic) work just fine.
        '    Object arrays (early or late bound) will work just fine.
        '
        ' Example:  If IsPtrDimmed(ArrPtr(SomeArray())) Then ...
        '
        Dim pSA As Long
        Dim cDims       As Integer
        Dim cElements   As Long
        '
        If pArray = 0& Then Exit Function                               ' Can happen on String arrays.
        GetMem4 ByVal pArray, pSA                                       ' De-reference.
        If pSA = 0& Then Exit Function                                  ' Dynamic undimensioned array.
        '
        GetMem2 ByVal pSA, cDims
        If cDims = 0 Then Exit Function                                 ' This would be unusual, but just in case.
        '
        GetMem4 ByVal PtrAdd(pSA, 16&), cElements
        If cElements = 0& And FailOnZeroNegOne Then Exit Function       ' Decide what to do about 0 to -1 arrays.
        '
        ' If we fell through to here, we should be looking at an array with elements.
        ' Or, we specified FailOnZeroNegOne=False, and it's one of those.
        IsPtrDimmed = True
    End Function
    
    
    

    Now, when focusing on that RetrieveResourceFileAndSaveToDisk procedure, you've got sFilePath, sFileName, sResourceType, and bOverwriteIfExists arguments. First, let's talk about getting the files into the resources of a project. That's done with the Resource Editor (and is discussed in that tutorial to which I supplied a link). For me, when I place files into the resources, I use the filename for the resource ID (and don't forget the extension). The Resource Editor will make it upper case and take out spaces, but that doesn't matter. Once it's in there, I might also give it a Type so I can keep different kinds of files separate. In my mind (for these files), these Types are like folders in my resources.

    Now, back to that RetrieveResourceFileAndSaveToDisk procedure. The sFilePath is pretty straightforward, and is just where the file is to be saved once it's pulled out of the resources. The sFileName is the original file name (with upper and lower case, and maybe also spaces). The sResourceType is that Type you specified when placing the file into the project's resources. And the bOverwriteIfExists is hopefully clear.

    That's about it. Also, you'll see code in the above that adjusts for the four-byte rounding on the Office files with four character extensions.

    Regarding "workflow", I think that depends on your needs. In my case, these Excel files are often templates I'll use to generate a report. So, I extract them when I'm starting a new report. However, you mentioned something about using them for a database ... so, you'll have to sort out for yourself when to extract them.

    And here's a post where I talked about the correct way to get the folder locations of places you may want to consider dropping these files from your resources.

    Good Luck,
    Elroy

    EDIT1: Just as a further FYI, the above works equally well regardless of whether you're executing from the IDE or a compiled EXE. If running from the IDE, it'll extract the file from the .RES file. If running from the .EXE, it'll extract it from the actual .EXE (which has the .RES file compiled into it).
    Last edited by Elroy; Jul 25th, 2019 at 09:22 AM.
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  6. #6
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    5,663

    Re: Attaching Excel database with the setup Packaging

    I'll gladly answer questions about the above post (#5). But I'd be more than willing to let others answer questions about Excel automation. I do a great deal of it, but I'm actually rather busy right now, and may not have time to get to these automation questions in a timely fashion ... and I know that many of us here have this expertise (even for the late-bound stuff).

    Take Care,
    Elroy
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  7. #7

    Thread Starter
    New Member
    Join Date
    Jul 2019
    Posts
    8

    Re: Attaching Excel database with the setup Packaging

    Thank you so much for your reply and the sample code. I will let you know how it works out for me. Thanks again!

  8. #8
    Frenzied Member
    Join Date
    Dec 2008
    Posts
    1,205

    Re: Attaching Excel database with the setup Packaging

    Why not create an excel file 'out of thin air'
    No excel automation required, to create the file.
    Excel Class - write to an XLS file without DLL's or Excel automation
    http://www.planet-source-code.com/vb...11898&lngWId=1
    HTH,
    Rob

  9. #9
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,345

    Re: Attaching Excel database with the setup Packaging

    So far, nobody has asked the question, why an Excel-File is needed at all...

    In the first post, the OP stated:
    "...best if the users cannot figure out that the database is, in fact, an excel file..."

    There is better and easier to use AppData-FileContainer-Formats for "tabular- or simple Key/Value-Raw-Data" IMO.
    E.g. JET 4 MDBs, or - even better - SQLite-DBs - would make a fine AppData-storage
    (even with built-in encryption if that's important).

    Olaf

  10. #10
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    5,663

    Re: Attaching Excel database with the setup Packaging

    Quote Originally Posted by Bobbles View Post
    Why not create an excel file 'out of thin air'
    Hi Rob,

    I don't know about Swop_K. However, in my case, I'm often starting with very sophisticated Excel templates (which are unwrapped from my resources when needed). To build some of these Excel templates from scratch with code would be an enormous (and unnecessary) task.

    I've really got no idea what Swop_K is doing. Building them from scratch may be a good choice for him.

    Best Regards,
    Elroy
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  11. #11
    Frenzied Member
    Join Date
    Dec 2008
    Posts
    1,205

    Re: Attaching Excel database with the setup Packaging

    Quote Originally Posted by Schmidt View Post
    So far, nobody has asked the question, why an Excel-File is needed at all...
    Olaf
    I was going to add a PS to my post, -
    "I personally would not use an excel file for a database, if someone held a gun to my head"
    but I was too polite.
    I was a Chartered Accountant and auditor, and then (because I blew the whistle too many times) I was ejected from the Auditing community.
    I then spent 20 years as a Senior Analyst in large bank, and telecommunication company.
    If you all knew the extent of use of Excel (in complex functions that should instead be proper programs with data bases), you would be staggered.
    Rob

  12. #12
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    5,663

    Re: Attaching Excel database with the setup Packaging

    Hey Rob,

    I also run into very similar situations, frequently. And I have set up so many (often VB6 but also often MS-Access VBA) front-ends for small MS-Access databases. And then, I try to convince people how easy it is to use the MS-Access visual query engine ... just drag-and-drop to create their queries (actually SQL Select queries, but that's all hidden from them).

    I don't know what it is, but it's an extremely rare case where they actually grab a-hold of the MS-Access querying capabilities, and some have good analytical minds too. Even in those cases, they frequently ask, "ok, so can I export this MS-Access table to an Excel file so I can use it?" They see me sigh, and I then show them how to do that.

    It's truly a mystery what the attraction to Excel is, but there certainly is one. I've seen secretaries spend hours figuring out something with Excel, and I show them how easy it would be with an MS-Access database, and they don't care.

    There's just truly some cultural mystique about Excel that attracts people to it like a moth to a light. I learned long ago to just quit fighting it and to accept it as a fact of life.

    Take Care,
    Elroy
    Last edited by Elroy; Aug 3rd, 2019 at 09:43 PM.
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width