Using variable instead of single file name for export to Excel, based on a form selection

Status
Not open for further replies.

Peter_W

New Member
Joined
Apr 2, 2018
Messages
29
Hello all,

There are various subs on the internet which export to an existing Excel file I have tried, and they work fine. But each one I've seen requires the use of a single file name such as SingleFileName.xls

Since my database had dozens of tables, which might be needed by dozens of related existing Excel files, I am trying to have a form button determine a variable string, which in turn will determine which single table in the d/b, and which single Excel file to use.

Every attempt I've tried has failed. For the code attached, I tried using a variable called ProjectID. But VBA errors out saying Compile Error - Object required. I totally lost on what that means. Seems like a variable should work for this code. Would any one know how to use a variable both the Excel file to export to, and use that same variable for the Access table in the d/b? Thank you for any responses.

Again the code works fine if I input a single file name, with a single table name. So the code works fine that way.





Sub ExcelExportFromAccess()


Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
Dim ProjectID As String
Set ProjectID = "Project123456"


blnEXCEL = False
' Replace True with False if you do not want the first row of
' the worksheet to be a header row (the names of the fields
' from the recordset)

blnHeaderRow = True

' Establish an EXCEL application object

On Error Resume Next
Set xlx = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0


' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True


' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file into which you will write the data


Set xlw = xlx.Workbooks.Open("C:\Users\pweston\Documents\Access Test\ProjectID & .xlsm")
' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
' (note that the worksheet must already be in the EXCEL file)
Set xls = xlw.Worksheets("TotalHistory")
' Replace A1 with the cell reference into which the first data value
' is to be written
Set xlc = xls.Range("A1") ' this is the first cell into which data go
Set dbs = CurrentDb()
' Replace QueryOrTableName with the real name of the table or query
' whose data are to be written into the worksheet
Set rst = dbs.OpenRecordset(ProjectID, dbOpenDynaset, dbReadOnly)


If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
If blnHeaderRow = True Then
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).value = rst.Fields(lngColumn).Name
Next lngColumn
Set xlc = xlc.Offset(1, 0)
End If
' write data to worksheet
Do While rst.EOF = False
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).value = rst.Fields(lngColumn).value
Next lngColumn
rst.MoveNext
Set xlc = xlc.Offset(1, 0)
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Sub
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,670
Office Version
2013
Platform
Windows
You probably should continue in the same thread, so I will close this one. Put your updated code there.

However, this again looks wrong, for the same reasons as before.
Code:
Set xlw = xlx.Workbooks.Open("C:\Users\pweston\Documents\Access Test\ProjectID & .xlsm")
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,102,200
Messages
5,485,309
Members
407,494
Latest member
RachelBuckland

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top