Exporting form records to Excel

[aatrv]

I'm currently working on a db in access and need help with some VBA. I have a form that has more than 10,000 records. There's a button on the form that can search for a specific record by entering the userID. I wanted to know if there's a way of generating a spreadsheet from a specific record? So i want to be able to type userID, press search, find the record for that user, and then have a button to save the record as a spreadsheet in excel. I want to make individual spreadsheets of each user. So basically I will have more than 10,000 spreadsheets. I already have a way to search for specific users, now I just want to save their record as a spreadsheet as it comes up after I search it. Also, I want to format the spreadsheet to include a title and also include the unique userID of the user at the top. Please let me know if that's possible and how I should approach it. I would greatly appreciate it. Thank you!

 

[delikedi]

You can produce a sample excel file, all formatted and all, note down the cells that change per each record in access, and save that file as a template (xltx extension). After this, you can write a VBA procedure that opens that template file, fills in the cells that you had noted down before with the data that you want from the access form, optionally saves the excel file with the name being the customer's name or something. You would tie that procedure to a new button that you would create on the access form, and with each different record you can create the excel file with just a click. You can also do this as a bulk operation that would create 10000 xlsx files in one go, which would require a few additional lines to your code.

 

[delikedi]

here's a boilerplate code. you need to create the template file as C:\MyTemplate.xltx for the code below to work. it simply writes Hello World to cell B2 and saves the file as "C:\MyTemplateAfterIFilledIt.xlsx"

Public Sub FillExcelTemplate()

Dim objXL As Object
Dim objXLBook As Object
Dim objXLSheet As Object
Dim objRange As Object

Dim strData As String

Set objXL = CreateObject("Excel.Application")
Set objXLBook = objXL.Workbooks.Open("C:\MyTemplate.xltx")
objXLBook.Application.Visible = True

Set objXLSheet = objXLBook.Worksheets(1)
Set objRange = objXLSheet.Cells.Range("B2")

strData = "Hello world"
objRange.value = strData

objXLBook.Saveas ("C:\MyTemplateAfterIFilledIt.xlsx")

Set objXL = Nothing
Set objXLBook = Nothing
Set objXLSheet = Nothing
Set objRange = Nothing

 

[aatrv]

This definitely seems like an easier way to do this than I was trying. I have a couple of questions. First, where do I actually insert this code? should I put it in the button's OnClick event or should I put this in a module and run it with a macro? Also, I understand how this will write "hello world" in the cell but how would I actually insert a particular record into the cell? For example, I would want to insert a specific: UserID , computer name, department, instead of hello world. I want to be able to pull up the record with my search button, then click another button that will save the record in an excel spreadsheet using the template you mentioned.

 

[delikedi]

I would create a button on that specific form and place the code in the button's onclick event. I'll give my reason at the end of my message.

In order to fill the template with individual fields of the record, you need the names of the controls that display those fields on your form. You can learn those by opening the form in design view, clicking a relevant control (i.e. the textbox that displays the customer number) and right-clicking to open properties. You already see the name of the control at the top of the properties pane that opens, but locate the "name" property under the "other" tab to be sure, nevertheless.

Once you determine the names of the relevant controls that display all the details of any record, all you have to modify is the line that prints "hello world" to a cell. For example, if the name of the control that displays the age of the customer is "txtCustomerAge" then you write

 objXLSheet.Cells.Range("B2") = txtCustomerAge

the rest of the code stays the same. you add as many lines as there are details on your form.

Now if you don't place the code in the onclick event of a button, it will mean that you have placed the code in a standard module, which means the code will not recognize all those control names. You would have to pass all those record details as parameters to a general function or procedure, which would require more overhead code. It would be redundant unless the info is very general info which is frequently required to be reported to excel in all kinds of contexts.

 

[aatrv]

hmm...seems pretty easy, but for some reason I keep getting an error. When I press debug, it highlights what I have in red below. not sure why because I did create a template, named it "MyTemplate.xltx" in the C drive. Also, since I will be using objXLSheet.Cells.Range("B2") = txtCustomerAge , Am I replacing this with the line I highlightted in green and also deleting the second line I highlighted in green?



Public Sub FillExcelTemplate()

Dim objXL As Object
Dim objXLBook As Object
Dim objXLSheet As Object
Dim objRange As Object

Dim strData As String

Set objXL = CreateObject("Excel.Application")
Set objXLBook = objXL.Workbooks.Open("C:\MyTemplate.xltx")
objXLBook.Application.Visible = True

Set objXLSheet = objXLBook.Worksheets(1)
Set objRange = objXLSheet.Cells.Range("B2")

strData = "Hello world"
objRange.value = strData

objXLBook.Saveas ("C:\MyTemplateAfterIFilledIt.xlsx")

Set objXL = Nothing
Set objXLBook = Nothing
Set objXLSheet = Nothing
Set objRange = Nothing

 

[aatrv]

Actually, you can ignore my last comment. I figured it out! it shows up just how I wanted in excel. Now I have one question. When I search a record on my form, it sometimes comes up with more than one page since theres more info for some records. For example, Person A is incharge of 20 books. When I enter his user ID and search, i get 20 pages on my form showing me all the books he's incharge of. One page has one book's info. And if I transfer that to excel, only the initial page shows up in my template. I want the rest of the books listed under this users excel report as well. Is it possible to display all the pages of that record onto the excel template?

 

[delikedi]

Interesting, I don't get an error on that line. You don't necessarily have to use a template file if that's causing the problem, you use the Saveas command towards the end after all. You can open and "saveas" normal xlsx files as well. Try another directory and an xlsx (or xls) file.

As for the greens, yes you don't have to use the strData variable anymore. Actually the line

Set objRange = objXLSheet.Cells.Range("B2")

is now redundant too, you refer to range B2 only once after all. All you need are multiple lines assigning Access fields to Excel cells, i.e your code would look like

Public Sub FillTemplate_Click()

Dim objXL As Object
Dim objXLBook As Object
Dim objXLSheet As Object
Dim objRange As Object

Dim strData As String

Set objXL = CreateObject("Excel.Application")
Set objXLBook = objXL.Workbooks.Open("C:\MyTemplate.xltx")
objXLBook.Application.Visible = True

Set objXLSheet = objXLBook.Worksheets(1)

objXLSheet.Cells.Range("B2")= UserID
objXLSheet.Cells.Range("C2")= UserName
'
'other lines here

objXLBook.Saveas ("C:\MyTemplateAfterIFilledIt.xlsx")

Set objXL = Nothing
Set objXLBook = Nothing
Set objXLSheet = Nothing
Set objRange = Nothing 	
End Sub

 

[delikedi]

Oh ok, didn't see your last post before posting my own.

Yes you can do that, but it requires additional code which may not be as easy to explain. From what I understand, you refer to "pages" in the sense of records. After you search a person, all records of books he is responsible of are listed in a subform that looks like a "page". If that is the case, you need to get to the form's underlying query and utilize that query as a recordset in your VBA procedure. If you are comfortable creating and handling queries and base forms on them, it might be easier to explain.

 

[aatrv]

I'm not too familiar with access actually :/ I just recently started using this for my internship. By the way, yes the records are listed in a subform that looks like a "page". I'm using Access 2010. When I search a user, his record comes up and at the bottom left of the form you can see "Record: 1 of 1000." Then there are buttons next to it to go to the next or previous subform. Is there a way to eliminate subforms so I can just have everything on a single page? then I can just transfer it to the excel template without any problems.