Full Version: Transferspreadsheet Not Recognising Named Ranges
I have a macro which transfers several queries from Access to Excel, based on named ranges that have been defined in the Excel spreadsheet. This was working fine, until my boss made some changes to the spreadsheet and now, instead of transferring the data to the ranges within the spreadsheet that have already been defined, it creates news tabs for each of the ranges and puts the data in them instead. The ranges in the spreadsheet have definitely been typed correctly in both Access and Excel, so I cannot see why it has suddenly started doing this. Has anyone got any ideas? I remember having this problem a couple of years ago as well, but don't think I ever did get to the bottom of it.
What exactly was it that your boss did to the spreadsheet? That would be the best way to start beginning to understand what is causing what here. Did he change columns, formatting, headers, sheet names, merge cells, etc?
Hi, sorry for being a bit vague. He did a few things, which involved inserting columns, changing lookups and re-defining some of the named ranges. However, I've even tried creating a new worksheet in the spreadsheet, creating a new range ('testrange'), and then in Access setting up a new macro with a single transferspreadsheet row to export a basic query to 'testrange'. But still, when I run this it creates a new tab within the spreadsheet called 'testrange' and dumps the data in there. When I look at named ranges, it has re-defined 'testrange' so refer to this new tab instead.
Have you tried naming the tab 'testrange' and seeing what happens then?
Also, I will post up a code I use personally to transfer my data from Access into Excel. Its a pretty big example, and does a lot more than just transfer the data, but you might find some of the portions of it useful.
You may also find this useful: Access MVP: KDSnell
KDSnell has some excellent information regarding transfering to excel.
Also, I will post up a code I use personally to transfer my data from Access into Excel. Its a pretty big example, and does a lot more than just transfer the data, but you might find some of the portions of it useful.
CODE
Private Sub Command0_Click()
cmbTeam.SetFocus
If MsgBox("Begin building and printing new Qualified Activity Matrix for " & cmbTeam.Text & " Team?", vbQuestion + vbYesNo) <> vbYes Then
MsgBox ("Action cancelled.")
'do nothing
Else
MsgBox ("Program will open the Excel file, update it, close it, and then print. This may take some time. Please be patient and wait for Excel file to close and print. Click OK to begin.")
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
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(Application.CurrentProject.Path & "\SOP\91-" & cmbTeam.Value & ".xls")
' 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("Main")
Dim rs As Recordset, db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT ID, [Effective Date] AS [EDATE], FORMAT(ID, '0000') AS [ID1], FORMAT(Acct, '0000') AS [ACCT1], FORMAT(CC, '0000') AS [CC1], FORMAT(DT, '00') AS [DT1], FORMAT(SP, '00') AS [SP1], FORMAT(GP, '00') AS [GP1], Title, Version FROM Documents WHERE ID=91")
xlw.Worksheets("Main").PageSetup.RightHeader = rs.Fields("Title")
xlw.Worksheets("Main").PageSetup.LeftFooter = rs.Fields("ACCT1") & "-" & rs.Fields("CC1") & "-" & rs.Fields("DT1") & "-" & rs.Fields("GP1") & "-" & rs.Fields("SP1") & "-" & rs.Fields("ID1")
xlw.Worksheets("Main").PageSetup.CenterFooter = "Rev: " & rs.Fields("Version")
xlw.Worksheets("Main").PageSetup.RightFooter = "Effective Date: " & rs.Fields("EDATE")
' 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()
Dim srcQ As String
srcQ = "TRANSFORM Format(First(srcSOP91.Date),'MM/YY') AS FirstOfDate SELECT srcSOP91.Warehouse, srcSOP91.HireDate FROM srcSOP91 WHERE (((srcSOP91.TeamID)=" & cmbTeam.Value & ")) GROUP BY srcSOP91.Warehouse, srcSOP91.HireDate, srcSOP91.TeamID PIVOT srcSOP91.Title;"
' Replace QueryOrTableName with the real name of the table or query
' whose data are to be written into the worksheet
Set rsT = dbs.OpenRecordset(srcQ, 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
End If
Workbooks(1).Worksheets("Main").Cells(2, 1).CopyFromRecordset rsT
rsT.Close
Set rsT = Nothing
dbs.Close
Set dbs = Nothing
' Close the EXCEL file while saving the file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close True ' close the EXCEL file and save the new data
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Workbooks.Open (Application.CurrentProject.Path & "\SOP\91-" & cmbTeam.Value & ".xls")
Excel.Application.DisplayAlerts = False
ActiveWorkbook.PrintOut Copies:=1, Collate:=True
ActiveWorkbook.Close False
Excel.Application.Quit
MsgBox ("Editing and printing complete.")
End If
cmbTeam.SetFocus
If MsgBox("Begin building and printing new Qualified Activity Matrix for " & cmbTeam.Text & " Team?", vbQuestion + vbYesNo) <> vbYes Then
MsgBox ("Action cancelled.")
'do nothing
Else
MsgBox ("Program will open the Excel file, update it, close it, and then print. This may take some time. Please be patient and wait for Excel file to close and print. Click OK to begin.")
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
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(Application.CurrentProject.Path & "\SOP\91-" & cmbTeam.Value & ".xls")
' 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("Main")
Dim rs As Recordset, db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT ID, [Effective Date] AS [EDATE], FORMAT(ID, '0000') AS [ID1], FORMAT(Acct, '0000') AS [ACCT1], FORMAT(CC, '0000') AS [CC1], FORMAT(DT, '00') AS [DT1], FORMAT(SP, '00') AS [SP1], FORMAT(GP, '00') AS [GP1], Title, Version FROM Documents WHERE ID=91")
xlw.Worksheets("Main").PageSetup.RightHeader = rs.Fields("Title")
xlw.Worksheets("Main").PageSetup.LeftFooter = rs.Fields("ACCT1") & "-" & rs.Fields("CC1") & "-" & rs.Fields("DT1") & "-" & rs.Fields("GP1") & "-" & rs.Fields("SP1") & "-" & rs.Fields("ID1")
xlw.Worksheets("Main").PageSetup.CenterFooter = "Rev: " & rs.Fields("Version")
xlw.Worksheets("Main").PageSetup.RightFooter = "Effective Date: " & rs.Fields("EDATE")
' 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()
Dim srcQ As String
srcQ = "TRANSFORM Format(First(srcSOP91.Date),'MM/YY') AS FirstOfDate SELECT srcSOP91.Warehouse, srcSOP91.HireDate FROM srcSOP91 WHERE (((srcSOP91.TeamID)=" & cmbTeam.Value & ")) GROUP BY srcSOP91.Warehouse, srcSOP91.HireDate, srcSOP91.TeamID PIVOT srcSOP91.Title;"
' Replace QueryOrTableName with the real name of the table or query
' whose data are to be written into the worksheet
Set rsT = dbs.OpenRecordset(srcQ, 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
End If
Workbooks(1).Worksheets("Main").Cells(2, 1).CopyFromRecordset rsT
rsT.Close
Set rsT = Nothing
dbs.Close
Set dbs = Nothing
' Close the EXCEL file while saving the file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close True ' close the EXCEL file and save the new data
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Workbooks.Open (Application.CurrentProject.Path & "\SOP\91-" & cmbTeam.Value & ".xls")
Excel.Application.DisplayAlerts = False
ActiveWorkbook.PrintOut Copies:=1, Collate:=True
ActiveWorkbook.Close False
Excel.Application.Quit
MsgBox ("Editing and printing complete.")
End If
You may also find this useful: Access MVP: KDSnell
KDSnell has some excellent information regarding transfering to excel.
I've just tried that, it just creates an additional tab called 'testrange1' instead
I edited my previous post, so have a look at it for some info. 
Thanks very much for this... the problem is, there are a lot of queries that need to be transferred (at least twenty), and I suspect that doing this through VBA would be a lot more time consuming than simply using the transferspreadsheet command within a macro (especially when there are a few people who may need to edit this at some point).
Well, the main important line in the code is:
This is pretty much where the magic happens. I've never personally used macro's, so I'm not really sure how it works, and I favor taking a programmers approach to everything - and I think with a little tweaking you could probably replicate the results.
Do you have a backup of the file before your boss made the changes somewhere? If you could test that, you can at least see if its the macro thats gone wonky, or if it was indeed the changes that were made to the spreadsheet.
CODE
Workbooks(1).Worksheets("Main").Cells(2, 1).CopyFromRecordset rsT
This is pretty much where the magic happens. I've never personally used macro's, so I'm not really sure how it works, and I favor taking a programmers approach to everything - and I think with a little tweaking you could probably replicate the results.
Do you have a backup of the file before your boss made the changes somewhere? If you could test that, you can at least see if its the macro thats gone wonky, or if it was indeed the changes that were made to the spreadsheet.
Yeah good plan. I do have a backup from before so will give it a go. If not, I may have to look at using code instead, as you suggest. Thanks for your help.
If you do indeed have to go the coding route, be sure to check out the KDsnell link I posted up earlier - it has pretty much every method possible of getting from data from access to excel, and vise versa.
Good luck with your project.
Good luck with your project.
Instead of "push to" from Access to Excel, have you considered a "pull from" Acess to Excel. That is, use MS-Query on the Excel side? MS-Query has the advantage of being able to duplicate formulas adjacent to the returned data range. It also means that it can be run "on demand" by the Excel user.
How exactly where the macros exporting to named ranges in the first place?
As far as I knew you couldn't specify a range to export to, you could only specify a range to import from.
That's with standard functions of course, if the 'macros' you were using is exporting to named ranges perhaps there's a bit more than a straightforward export going on?
As far as I knew you couldn't specify a range to export to, you could only specify a range to import from.
That's with standard functions of course, if the 'macros' you were using is exporting to named ranges perhaps there's a bit more than a straightforward export going on?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.