Solved

Autopopulate excel file from access through VBA

Posted on 2014-03-19
23
2,047 Views
Last Modified: 2014-04-08
Hi ,
I was wondering if it is possible. I need to autopopulate excel file with information recorded in access table/query. Preferably using VBA.
The table tbl_Electricity has same Property ID's as in excel file. Based on that property ID I need to update usage in excel file.

I have attached sample Excel File and Access file.
Any help would be apreciated
Template-Sample.xlsx
Sample-File.accdb
0
Comment
Question by:maximyshka
  • 8
  • 7
  • 7
  • +1
23 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 39940581
I can't look at either attachment where I am.  To export data to Excel from Access, use the TransferSpreadsheet method.  This works well as long as what you are exporting is a "list".  It can have headers or not and it can be directed to a specific sheet in the workbook based on the options in the statement.  If you have data that you want to poke hither, thither, and yon, you will need to do it with OLE automation code.
0
 

Author Comment

by:maximyshka
ID: 39940604
I think that I need to use OLE automation code because I don't need to export the whole table only some fields. Do you know any good examples of code?
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39940826
This is possible with ACCESS VBA.  I have written several routines that will export a record set to an Excel workbook.  If you are interested, I can post a few examples, when I get back in the office tomorrow.

Tom
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 39941002
The TransferSpreadsheet works with a table or a query.  In fact, queries and tables are interchangeable for most things in Access.  From the looks of the spreadsheet, you don't need automation code.  Just make a query that selects the columns and rows you want to export.  If the criteria isn't fixed, use criteria in the query that references form fields so you  can validate the criteria if necessary before running the export..
0
 

Author Comment

by:maximyshka
ID: 39942056
@pathartman do you have code examples, I need to update data in the spreadsheet based on property id in the spreadsheet.
0
 
LVL 10

Expert Comment

by:broro183
ID: 39942181
hi,

Here is an example of using a query (within Access) that selects the columns you want to export (qry_ElectricityLtdCols = query of Electricity returning Limited Columns). Note, I have not applied any grouping to the result set or filtered which property id's are being returned. You can adjust this as required.

This is a simplistic example & the comments of PatHartman & Tom suggest that they can provide better or more complex examples if needed.

'qry_ElectricityLtdCols
SELECT tbl_Electricity.[Property ID], tbl_Electricity.[Start Date], tbl_Electricity.[End Date], tbl_Electricity.Usage
FROM tbl_Electricity;

Open in new window


In excel you can create an external connection to the new query & use it as the data source for a table on a separate sheet. This sheet can be hidden.
Then you can populate the relevant cells on "Add Bills-Electricity" worksheet using an equation like:
=INDEX(Table_Sample_File.accdb,MATCH('Add Bills-Electricity'!$C2,Table_Sample_File.accdb[Property ID],0),MATCH('Add Bills-Electricity'!G$1,Table_Sample_File.accdb[#Headers],0))

Open in new window


Note, the data set/table may change so you have to decide if you want the cells to stay as formulae, or if you want them to be converted to hardcoded values after certain events.

hth
Rob
Template-Sample.xlsx
Sample-File.accdb
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39942287
docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12,"queryname","filename",true

As you type the expression, intellisense will help you build it.  OR, you can call up the help entry and read about the arguments so you understand what is required.  When filename points to a non-existent file, Access will create it.  If the file already exists, Access will update it.  "queryname" will be used as the name of the worksheet.
0
 

Author Comment

by:maximyshka
ID: 39942498
@broro183 your solution is working, but I would prefer to have it run from access code. If possible. So user can select a file and this file will be updated with information from database. i do not want users to touch excel files. Those files produced by another system.

@PatHartman I need to update data based on the propertyid field, i don't htink that it is possible with TransferSpreadsheet
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39943017
Why would you not just replace all the data?  Having two applications responsible for updating data is a recipe for disaster.  How would you ever know the Access data is more current than what is in the spreadsheet if people can update both?   And, if they can't update both, then one should link to the other or import the complete recordset and not attempt to update individual records.
0
 

Author Comment

by:maximyshka
ID: 39943631
This is data exchange between two separate and independent entities. Entity A provide excel files with a data request, they do it on demand that is why i am tasked to impelment automated procedure. After data is populated in excel file , then it is uploaded into their system and excel file is parsed into Entity A database. Excel file cannot be changed, otherwise it cannot be parsed. There are no communications between two systems and they are totaly independent from each other.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39943718
Are you saying that you have to send back the exact, same spreadsheet but with modified data?  Are you sure you can't replace the whole set?  The TransferSpreadsheet method is a single line of code.  It could even easily be done as a macro if you are not comfortable with VBA.  OLE automation is something entirely different.  You will need to understand VBA and it will take significantly more effort so I would not do it if I didn't need to.

I don't have any code samples to offer.  I occasionally write code to poke values into specific cells but the locations are fixed and known ahead of time.  You will need write a code loop that reads your Access query and finds the matching row in Excel.  This process will be very time consuming to run if you have more than a few hundred rows.  In that case, I would start by sorting the worksheet into key sequence and using a query that is also in key sequence.  That way you will only have to make one pass through the file to match all the records.

Since I rarely write Excel VBA, I am not familiar with its object model.  If I need to do something I haven't done before, I open Excel and turn on the macro recorder.  I step through the process and then stop the recorder and examine the generated VBA code.  It can usually be used with little modification in Access.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 10

Expert Comment

by:broro183
ID: 39944379
hi,

I'm familiar with the excel object model, but as Pat says coding will take more time. I hope to get a chance to look at this in more detail during the weekend.
Yes, it's possible to run from MS Access code. Would you be happy with the implementation that I have shown if MS Access code is used to initiate/execute the update?
If the user can select a file, what validation checks would be needed/desired?
For example:
- what happens if the cells are already populated on the Add Bills worksheet?
- Does it over-write all rows in the spreadsheet, or only the blank ones?
- if the user chooses a file incorrectly, how does the macro know?
  One of the safer approaches is to force the user to review the excel file & save it manually, but this goes against your desire to prevent interaction.

Instead of my suggested solution, a recordset could be used from within Accesss, which picks up the data in the spreadsheet & holds it in memory as a variant array (it's faster than cell by cell looping), processes all the records,adds the info into the relevant elements & drops the results back in the spreadsheet in one go.

As Pat has mentioned, processing will be faster if both the spreadsheet & the Access data sets are sorted. Is there only ever a single line for each PropertyID in the spreadsheets & in Access?

Rob
0
 

Author Comment

by:maximyshka
ID: 39945149
@broro183 - Thanks a lot it will help me a lot. I do some programming in vba, but nothing fancy so definetely will apreciate your help. I can't resolve it on my own.
Would you be happy with the implementation that I have shown if MS Access code is used to initiate/execute the update?
Yes, Just can't change excel file itself. And can't add any formula. Otherwise it will not be parsed correctly by receiving application

If the user can select a file, what validation checks would be needed/desired?
For example:
- what happens if the cells are already populated on the Add Bills worksheet?
The data from the database always overwrites the data in excel.
- Does it over-write all rows in the spreadsheet, or only the blank ones?
It overwrites all fields , I just want propertyid to stay the same. Since it represents the key.Otherwise data from database always wins.
- if the user chooses a file incorrectly, how does the macro know?
Don't need that check
 One of the safer approaches is to force the user to review the excel file & save it manually, but this goes against your desire to prevent interaction.[/quote
]
Correct, I do not want user to touch files. There are alot of files like that . Would be cool if user can select directory and access can go through files and insert data based on property id in all of them without opening the files. But one by one method will work too.

Thnaks a lot for your help!
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39945694
@broro183,
It sounds like you are offering to write this  function for the OP.  That is not what this forum is about but it is your choice if you are OK working for free.  I still haven't heard anything that precludes the use of TransferSpreadsheet which is a one line of code solution once you get past selecting the target spreadsheet.  By naming the query as the name of the sheet you want to replace, Access will write the selected data to the specified sheet of an existing workbook.
0
 
LVL 10

Expert Comment

by:broro183
ID: 39951428
hi,

maximyshka,
It seems that there are more columns in the spreadsheet than there are in the database. Can you please provide a list of all fields/columns that get over-written?
Is the sheet name going to be the same in every file?
Is there only one sheet in every file?
Are the fields in the same columns in every file?

Pat,
I'm here to help and, while I haven't actually been involved with the paying side of this site, I'm willing to work for free on this thread.
I haven't heard anything that precludes its use either. If you provide a working example maximyshka may understand how it works & the thread can be marked as solved.

Rob
0
 
LVL 10

Accepted Solution

by:
broro183 earned 250 total points
ID: 39953322
hi Maximyshka,

Here is an initial attempt to locate files & then use a recordset to update the data. It is not thouroghly tested though & is likely to need some modification. Give it a go and post back if you have any questions.

Note this still uses early binding in places so further changes (to late binding) may be useful.

Option Compare Database
Option Explicit

'sourced from Access Help files.
'DoCmd.TransferSpreadsheet TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA
'DoCmd.TransferSpreadsheet acImport, 3, "Employees","C:\Lotus\Newemps.wk3", True, "A1:G12"
' => DoCmd.TransferSpreadsheet acExport, 9, "tbl_Electricity", FnameArr(0)(i), True
'sourced from: http://www.utteraccess.com/forum/index.php?showtopic=1994414&view=findpost&p=2291959
'acSpreadsheetTypeExcel12 --> 9 --> this is to export as .xlsb
'acSpreadsheetTypeExcel12xml --> 10 --> This is to export as .xlsx

Sub OpenExcelFilesUpdateColumnsThenSaveAndClose_v2()
'some code modified after being sourced from
'http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28290367.html#a39639876

'Const strPath As String = "S:\CP Production Data\COMMSMASTFOLD\"    '#### change as required
Dim xlApp As Excel.Application    'Object '
Dim xlWb As Excel.Workbook
Dim xlWsToBeUpdated As Excel.Worksheet
Dim xlTemporaryWsWithNewAccessData As Excel.Worksheet
Dim xlWsToBeUpdatedLastRow As Long
Dim xlTempWsLastRow As Long
Dim ThisDb As String
Dim OriPath    ' Original path
Dim FnameArr As Variant
Dim i As Long
Dim UserFB As String    'user feedback
Dim rs As DAO.Recordset
Dim HdrArr() As String
Dim NumOfOutputCols As Long
Dim HdrInd As Long    'Header Index

    ThisDb = CurrentDb.Name
    Set rs = CurrentDb.OpenRecordset("tbl_Electricity", dbOpenDynaset)
    '"C:\Users\Robert\Downloads\Sample for EE\Sample-File.accdb"
    Set xlApp = CreateObject("Excel.Application")
    'for testing
    xlApp.Visible = True

    'identify the current directory & then change it to the desired path before using getopen filename
    OriPath = CurDir
    '    ChDir strPath
    'or ###
    ChDir CurrentProject.Path

    FnameArr = GetOpenFileNameForAccess(True, "All Files, *.*, Excel Files,", "*.xl*;*.xls;*.xlt", 1, CurrentProject.Path)

    If IsArray(FnameArr) Then
        For i = LBound(FnameArr) To UBound(FnameArr)
            Set xlWb = xlApp.Workbooks.Open(FileName:=FnameArr(0)(i))

            Set xlWsToBeUpdated = xlWb.ActiveSheet
            ''or if files will have more than one sheet, do all the target worksheets have a specific name, for example will the following line work?
            'Set xlWsToBeUpdated = xlWb.Worksheets("Add Bills-Electricity")

            xlWb.Worksheets.Add
            Set xlTemporaryWsWithNewAccessData = xlWb.ActiveSheet

            NumOfOutputCols = rs.Fields.Count
            ReDim HdrArr(0 To 0, 0 To NumOfOutputCols - 1) As String
            For HdrInd = 0 To NumOfOutputCols - 1
                HdrArr(0, HdrInd) = rs.Fields(HdrInd).Name
            Next HdrInd
            
            With xlTemporaryWsWithNewAccessData
                '**************************************
                .Range("a1").Resize(1, NumOfOutputCols).Value2 = HdrArr
                .Range("a2").CopyFromRecordset rs
                ' the above 2 lines can probably be merged by use of this single line:
                DoCmd.TransferSpreadsheet acExport, 9, "tbl_Electricity", FnameArr(0)(i), True
                xlTempWsLastRow = .UsedRange.Rows.Count
                .ListObjects.Add(xlSrcRange, .UsedRange, , xlYes).Name = "Table_OfNewAccessData"
            End With

            With xlWsToBeUpdated
                xlWsToBeUpdatedLastRow = xlApp.WorksheetFunction.Max(2, .Range("C" & .Rows.Count).End(xlUp).Row)
                With .Range("D2:J" & xlWsToBeUpdatedLastRow)
                    '.ClearContents
                    .FormulaR1C1 = "=INDEX(Table_OfNewAccessData,MATCH('" & xlWsToBeUpdated.Name & "'!RC3,Table_OfNewAccessData[Property ID],0),MATCH('" & xlWsToBeUpdated.Name & "'!R1C,Table_OfNewAccessData[#Headers],0))"
                    .Value = .Value
                    .Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
                             SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                             ReplaceFormat:=False
                End With
            End With
            xlWb.Close savechanges:=True
            Set xlWb = Nothing

        Next i
        UserFB = "Finished"
    Else
        UserFB = "No files selected therefore macro has ended."
    End If

    'change back to user's original path
    ChDir OriPath

    MsgBox UserFB
    Set rs = Nothing
    Set xlWsToBeUpdated = Nothing
    Set xlTemporaryWsWithNewAccessData = Nothing
    Set xlWb = Nothing
    xlApp.Quit
    Set xlApp = Nothing

End Sub

Function GetOpenFileNameForAccess(MultiSelect As Boolean, FFilterDesc As String, _
                                  FFilterExts As String, FFilterPos As Long, Optional iniFName As String) As Variant
'Function GetOpenFileNameForAccess(MultiSelect As Boolean, FilterIndex As Long, FFilterDesc As String, _
  FFilterExts As String, FFilterPos As Long, Optional iniFName As String) As Variant
'Sourced & modified from http://www.mrexcel.com/forum/microsoft-access/414183-how-do-you-change-current-directory-use-getopenfilename.html#post2051658
Const msoFileDialogFilePicker As Long = 3
Const msoFileDialogViewDetails As Long = 2
Const Delim_Char As String = "|"
    'Declare a variable as a FileDialog object
    'reference you would set would be Microsoft Office xx.0 Object Library (where xx is a number like 11 (2003) or 12 (2007).
Dim fd As FileDialog
    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant
Dim TempStr As String

    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .AllowMultiSelect = MultiSelect
        '.FilterIndex = FFilterPos 'FilterIndex
        .Filters.Add FFilterDesc, FFilterExts, FFilterPos
        'Set the initial path based on the argument passed to the function.
        .InitialFileName = iniFName
        'Set the initial view to the details view.
        .InitialView = msoFileDialogViewDetails
        .Title = "Get open file name(s) as strings..."
        'Use the Show method to display the File Picker dialog box and return the user's action.
        'If the user presses the action button...
        If .Show = -1 Then
            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems
                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                '***************
                TempStr = TempStr & Delim_Char & vrtSelectedItem
            Next vrtSelectedItem
            TempStr = Right(TempStr, Len(TempStr) - Len(Delim_Char))
            GetOpenFileNameForAccess = Array(Split(TempStr, Delim_Char))
        Else
            'If the user presses Cancel...
            GetOpenFileNameForAccess = False
        End If
    End With
    '   Set the object variable to Nothing.
    Set fd = Nothing
End Function

Open in new window


hth
Rob
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39953752
157 lines of code and growing vs 1 line of code.
0
 

Author Comment

by:maximyshka
ID: 39954131
thank you for your help. I will try to implement it  and let you know how it works. Thanks a lot for your help!
0
 
LVL 10

Expert Comment

by:broro183
ID: 39954439
maximyshka,

I agree with Pat's repeated suggestion of using "DoCmd.TransferSpreadsheet" & I have included an example in my last comment of how I think you could use it:

DoCmd.TransferSpreadsheet acExport, 9, "tbl_Electricity", FnameArr(0)(i), True

Open in new window

(see the commented lines at the top of the code & line 70)
Have a think about what (if any) modifications you would need to make to your existing process to allow the use of DoCmd.TransferSpreadsheet. Try to think outside the box too & consider my outstanding questions from my previous comment. Or perhaps, can you explain why you can't use the "one line approach"?

Pat,
Yes, there are a lot of lines of code, & it could be coded to run faster (ie process the recordset in memory, probably by using more code lines!) but the above is my initial offering. I think it meets the OP's perceived requirements & "perception is reality" until a person's perception is changed.
My offering does include a wrapper too for processing multiple files in response to the scope creep of this comment. So, in reality, for the "one liner" to have the same functionality, it would become approximately 10 lines ;-)

Rob
0
 

Author Comment

by:maximyshka
ID: 39973388
sorry had an emergency at work last week couldn't work on it. I was able to work on it today.
Both methods produced the same result, so they  are correct 1 line of code is working.  Some reason i though that it will not work, since i also wanted to keep a log what's happening.

Thank you for your help I appreciate it a lot and I actually learned a lot!!!

Thank you again!
0
 
LVL 10

Expert Comment

by:broro183
ID: 39974452
I'm pleased we could help & that you have learned a lot. I hope you use the one line approach because it is much easier for anyone to maintain a single line of code, than for the many lines of my suggestion.

Rob
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39975209
Rob,
I wasn't being critical of the code.  It is fine.  What I object to is writing 157 lines of code when only one is necessary.  I've been doing this a long time and I've written my million lines of code so I don't need the practice.  That is one of the things I love about Access.  You can do so much with so little.  What newcomers need to understand is that every line of code you write must be thoroughly tested.  It also must be executed every time you need to run the process and when you weigh an application down with excess code it becomes harder for others to understand or change and can even become sluggish.  In my experience though, the worst Access apps are those written by experienced .net programmers.  They quite literally write thousands of lines of code and create classes nested 7 deep to replicate built in functionality because they don't have similar features in their environment and so don't expect them in Access and therefore never even look for them because they're "real" programmers.  At least non-programmers make an effort to poke around or ask for help to learn the "Access way".
0
 
LVL 10

Expert Comment

by:broro183
ID: 39985640
hi Pat,

Thank you for your comments - I've just seen your post and appreciate you taking the time to let me know. I'm not counting but I'd guess that I have a long way to go before I hit one million LoC so I am still practicing (esp in MS Access vba). I do understand what you mean about needing to firstly test and then to understand & maintain code. The "worst" coding/apps that I've seen are at the other extreme, effectively non-programmers who don't poke around. These have usually been people who use Excel's macro recorder & never-never-ever review/change/refactor the recorded code which has tens to hundreds of unnecessary LoC, although I have also seen it with copy & paste coders too. Anyway, that's enough hijacking for now...

Pat, would you mind looking in on this question which is about using DoCmd.TransferSpreadsheet to update an excel file that is saved in SharePoint?


Rob
Always learning & the best way to learn is to experience...
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now