Which Export to Excel Process is best to copy data into an existing formatted spreadsheet? (1 Viewer)

JMongi

Active member
Local time
Today, 13:23
Joined
Jan 6, 2021
Messages
802
As I usually do, as my first Access project I have an ambitious plan to create a standalone Access app for use by many in our company to manage various appropriate buckets of data (inventory, purchasing, asset management, etc). As a stepping stone in dragging our companies data kicking and screaming into the modern age, and to demonstrate some ownership of this data in advance of the full blown rollout, I would like to export the results of a few queries into an excel spreadsheet for use by the powers that be in a way they are currently used to doing.

To that end, I would create a basic Excel template file that is formatted appropriately (sheet headers, column headers, any formulas, etc). Then, I would like to export data out of an Access query and into this excel template file without affecting the existing formatting. I've searched and found quite a few links to various methodologies. I don't know any of them very well, so, before I spend a lot of time figuring them out, I thought I would focus my energies on the one(s) that can accomplish my stated goals.

There was one methodology involving copying a recordset but it wasn't clear if the copying process overrode the existing formatting in the target spreadsheet.

Thanks for your input!
 

Minty

AWF VIP
Local time
Today, 17:23
Joined
Jul 26, 2013
Messages
10,355
In my experience, it's generally easier to format the spreadsheet using automation, after chucking the data into it.
You can pretty much do anything with it, from formatting as a table, adjusting columns widths and number/date formats, adding formulas conditional formatting etc. etc.

It can be a bit of a learning curve.

The alternative, which can work well depending on the complexity, is to use a template file, and dump the data into a simple "Data" worksheet and drive your templated outputs from that.
 

JMongi

Active member
Local time
Today, 13:23
Joined
Jan 6, 2021
Messages
802
So, just to make sure I understand correctly. The second option involves data dump into one sheet with links to the formatted sheet?

I'm not opposed to method #1 at all but the "learning curve" involves time. I've done enough VBA to be danger and I've done more mucking around in Excel than Access but I was still worried about how much time it would take to learn how to automate the formatting. I suppose I could dip my toe and see how fast the basics come together...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:23
Joined
May 7, 2009
Messages
19,169
you can export it on a Formatted worsheet using vba and recordset

sheets(1).range("A1").CopyFromRecordset rs
 

Minty

AWF VIP
Local time
Today, 17:23
Joined
Jul 26, 2013
Messages
10,355
The biggest slowdown (as a medium level excel user) for me is getting the "bloody syntax" right in Access VBA.
You have to refer to the complete object, whereas in VBA in Excel sort of works it out for you.

@Isaac and @arnelgp have done a lot of Excel stuff, so maybe better placed to advise you.
 

JMongi

Active member
Local time
Today, 13:23
Joined
Jan 6, 2021
Messages
802
@arnelgp - So, that method is the one I was referencing in my first post. So, just to confirm, that method does not overwrite any existing formatting in the target worksheet?
 

JMongi

Active member
Local time
Today, 13:23
Joined
Jan 6, 2021
Messages
802
The biggest slowdown (as a medium level excel user) for me is getting the "bloody syntax" right in Access VBA.
You have to refer to the complete object, whereas in VBA in Excel sort of works it out for you.

@Isaac and @arnelgp have done a lot of Excel stuff, so maybe better placed to advise you.
It would definitely be cleaner to generate a new spreadsheet and I may get there sooner rather than later, but creating the template should be relatively quick if I can just export the data into it.
 

JMongi

Active member
Local time
Today, 13:23
Joined
Jan 6, 2021
Messages
802
Well, I couldn't help myself and I'm investigating creating the formatting upon spreadsheet generation using VBA. Question, though....

One of the functionality I'm trying to replicate is clicking on a machine's unit number from this list and having it launch the file explorer to the shared documents on our server pertaining to that unit number. Here is the code I'm using in the functioning Access form:

Code:
Private Sub UnitNum_Click()
   Dim Shex As Object
   Dim frmList As Form
   Dim UnitNum As control
   Dim Loc As String
   Dim Loc2 As String
   Dim tgtfile As String
   Dim tgtfile2 As String
'Set object definitions
   Set frmList = Forms("JeffAvailability")
   Set UnitNum = frmList.UnitNum
   Set Shex = CreateObject("Shell.Application")
'Pull the unit number and set the folder location
   Loc = UnitNum.Value
   tgtfile = "\\**serverpath**\1 - UNIT FILES\" & Loc & "\"
   Debug.Print tgtfile
  
   If Not Dir(tgtfile, vbDirectory) = vbNullString Then 'make sure folder exists
        Shex.Open (tgtfile)
   Else
        'Check if a unit file without year prefix exists
        Loc2 = Mid(Loc, 4)
        tgtfile2 = "\\**serverpath**\1 - UNIT FILES\" & Loc2 & "\"
        Debug.Print tgtfile2
        If Not Dir(tgtfile2, vbDirectory) = vbNullString Then 'make sure folder exists
            Shex.Open (tgtfile2)
        Else
            MsgBox "The Unit File Does Not Exist"
        End If
   End If
  
End Sub

So, aside from figuring out how to reroll this VBA in Excel, is it possible to generate an Excel spreadsheet with VBA already incorporated in it?
 

JMongi

Active member
Local time
Today, 13:23
Joined
Jan 6, 2021
Messages
802
I suspect I'll need at the very least a separate available text file with my VBA code in it to pull into the Excel file after creation or reference in some way...
 

JMongi

Active member
Local time
Today, 13:23
Joined
Jan 6, 2021
Messages
802
@Minty, @arnelgp, @Isaac - So, I am rerolling my file explorer launch in the Excel template and have run into a small snag. Here is the code:

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Column = A & Target.Value Is Not Null Then
    Dim Shex As Object
    Dim Unit As String
    Dim UnitTrunc As String
    Dim ServerPath As String
    Dim tgtfile As String
    Dim tgtfile2 As String
   
    'Set object definitions
    Set Shex = CreateObject("Shell.Application")
   
    'Set the unit number and the folder locations
    Unit = Target.Value
    UnitTrunc = Mid(Unit, 4)
    ServerPath = "\\**serverpath**\1 - UNIT FILES\"
    tgtfile = ServerPath & Unit & "\"
    tgtfile2 = ServerPath & UnitTrunc & "\"
    Debug.Print tgtfile
    Debug.Print tgtfile2
 
    If Not Dir(tgtfile, vbDirectory) = vbNullString Then        'check if regular unit file exists
        Shex.Open (tgtfile)
    Else
        If Not Dir(tgtfile2, vbDirectory) = vbNullString Then   'Check if a unit file without year prefix exists
            Shex.Open (tgtfile2)
        Else
            MsgBox "The Unit File Does Not Exist"
        End If
    End If
End If
End Sub

I tested by changing the first If statement to check against a specific value and it opened a file explorer as programmed. So I know the launch code itself is working.
So, what the first if statement is supposed to check is: 1. if the cell is in column A (the only values that matter for this sub), the cell contains text (so that it doesn't try and open anything for blank cells).

I'm searching for formatting of the target. class but thought I would post this to the gurus here! Also, still awaiting an answer on any way to dynamically generate an excel spreadsheet from Access that includes embedded VBA code in the spreadsheet itself.
 
Last edited:

Minty

AWF VIP
Local time
Today, 17:23
Joined
Jul 26, 2013
Messages
10,355
I'm not sure why you are opening excel with a shell execute the more 'normal' route would be something like
Rich (BB code):
    ' Late binding to avoid reference:
    Dim xlApp            As Object        'Excel.Application
    Dim xlWb             As Object        'Workbook
    Dim xlWS             As Object        'Worksheet
    Dim tbl              As Object
    Dim rng              As Object
 
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlWb = xlApp.Workbooks.Open(sFile)
    'Debug.Print xlWB.Name
    Set xlWS = xlWb.Worksheets(sSheet)

Where you supply the file name (sFile) and worksheet name (sSheet)
 

JMongi

Active member
Local time
Today, 13:23
Joined
Jan 6, 2021
Messages
802
@Minty - I'm not using Shell to open Excel, I'm using shell to open a file explorer window. The code I posted would be run from within Excel to open a specific server location based on the value in the cell that has been double clicked. The code was taken pretty much verbatim from a form control in Access that does the same thing (open a server location based on contents of the control).
 

Minty

AWF VIP
Local time
Today, 17:23
Joined
Jul 26, 2013
Messages
10,355
@Minty - I'm not using Shell to open Excel, I'm using shell to open a file explorer window. The code I posted would be run from within Excel to open a specific server location based on the value in the cell that has been double clicked. The code was taken pretty much verbatim from a form control in Access that does the same thing (open a server location based on contents of the control).
Sorry I mis-read your post completely, and missed the top part of it.
I'll re-read and try again. 1/10 poor effort.
 

JMongi

Active member
Local time
Today, 13:23
Joined
Jan 6, 2021
Messages
802
No worries! I haven't even had any coffee today. I'm pretty sure it's illegal to code without caffeine! :coffee::cool:
 

Minty

AWF VIP
Local time
Today, 17:23
Joined
Jul 26, 2013
Messages
10,355
So to check a specific cells contents you would normally use. something like

Code:
Dim rMyRange as Range

rMyRange = Range("A1")

If IsEmpty(rMyRange) Then ....
 

JMongi

Active member
Local time
Today, 13:23
Joined
Jan 6, 2021
Messages
802
So, part of the issue is that Target.Column or .Columns isn't returning what I thought it would. Here are some examples:
Target.Columns(1) = Cell Value
Target.Column = 1
Target = Cell Value
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:23
Joined
May 7, 2009
Messages
19,169
is this a combo/listbox?
use Range or Cells if you are using excel.
 

JMongi

Active member
Local time
Today, 13:23
Joined
Jan 6, 2021
Messages
802
@Minty - That is the general idea, however, I am less interested in the value of the cell itself for checking and more interested in its location. I only want the doubleclick procedure to run full through on cells in column A that are populated. It should do nothing if unpopulated cells in column A are doubleclicked or cells in other columns are doubleclicked. In this particular case, the range is predefined "ByVal Target as Range"

@arnelgp - These are normal cells in an Excel spreadsheet, no controls.
 

Minty

AWF VIP
Local time
Today, 17:23
Joined
Jul 26, 2013
Messages
10,355
You've lost me. A cells location is always the same A1 is A1 - always.
Can you take a step back and explain in simple terms what you are trying to achieve. I have a feeling you are trying to use a Hadron Collider to crack a nut here.

If you are searching for some content and want to return the location of that content that is a different matter.
If you want to get the location of the cell that was double-clicked that is also a different result.
 

Users who are viewing this thread

Top Bottom