RunSavedImportExport vs. TransferSpreadsheet (1 Viewer)

[eddie84]

I'm trying to export a sorted Query into Access via the BE. The query represents data required for a drop-down in Excel. Essentially, the goal is, if records are changed (added or deleted) on the Access side, I want these changes to be exported (pushed) to Excel. So first step is to create a function that will do the export..the next step for me would be to create a refresh button..where a click of refresh would lead to the drop-downs being updated on the Excel side. (so i guess re-query would work here?)

I know there is a newer way to importexport (which appears to be RunSavedImportExport) and the older way (TransferSpreadsheet). I'm just not sure if they are really mutually exclusive in a lot of cases? This is where I would need your expertise.

Regards

 

[theDBguy]

Hi. Welcome to AWF!

Both methods are still available. Running a Saved Import/Export is more rigid than using TransferSpreadsheet, because you cannot specify the parameters when using the former. So, if you're always importing/exporting to the same filename and location, then you can use RunSavedImportExport. Otherwise, you should use TransferSpreadsheet.

 

[eddie84]

Hi. Welcome to AWF!

Both methods are still available. Running a Saved Import/Export is more rigid than using TransferSpreadsheet, because you cannot specify the parameters when using the former. So, if you're always importing/exporting to the same filename and location, then you can use RunSavedImportExport. Otherwise, you should use TransferSpreadsheet.

Thank you. This makes sense.

The name/path would actually be constant, so I am fine with using RunSavedImportExport.

But two things I can't seem to figure out:
a. how do I export from Access to Excel to a specific Sheet (Worksheet) that exists in that Workbook? I keep replacing the WHOLE excel workbook.
b. The sorted query in Access, that I'm attempting to export, does not completely have the same Field Names. (even though each field represents the same data/information). Is this a problem?

 

[theDBguy]

Thank you. This makes sense.

The name/path would actually be constant, so I am fine with using RunSavedImportExport.

But two things I can't seem to figure out:
a. how do I export from Access to Excel to a specific Sheet (Worksheet) that exists in that Workbook? I keep replacing the WHOLE excel workbook.
b. The sorted query in Access, that I'm attempting to export, does not completely have the same Field Names. (even though each field represents the same data/information). Is this a problem?

Yes, I think that's a problem. It sounds to me more like, you can just use TransferSpreadsheet and not create any SavedImportExport specs at all, since you're not really going to use them again (at least, not for this particular scenario).

If you use TransferSpreadsheet, you can use the Range argument to specify which Sheet you want to use in the Spreadsheet to accept your data from Access.

 

[eddie84]

Yes, I think that's a problem. It sounds to me more like, you can just use TransferSpreadsheet and not create any SavedImportExport specs at all, since you're not really going to use them again (at least, not for this particular scenario).

If you use TransferSpreadsheet, you can use the Range argument to specify which Sheet you want to use in the Spreadsheet to accept your data from Access.

Yes. This is what I was thinking as well.

How about the difference in field names?

 

[theDBguy]

Yes. This is what I was thinking as well.

How about the difference in field names?

When you use TransferSpreadsheet, you can specify to use a table or a query to export. So, if you have different requirements, such as different fields to export, one approach is to create separate queries for them.

 

[eddie84]

When you use TransferSpreadsheet, you can specify to use a table or a query to export. So, if you have different requirements, such as different fields to export, one approach is to create separate queries for them.

Hmm, not following you on this one.

To give you an example;
In Access, I have a table called 'Locations'. I create a query of this for the purposes of Exporting it to Excel. In Excel, I also have a sheet named Locations. BUT, while both the table in Access and Sheet in Excel represent the same data..the Access field names I am exporting to Excel have different Field names than the ones in Excel.

How does export handle this? How does it even know to replace that data properly? (since it can't compare Field names?) I understand that it would know the right Sheet..but how about comparing values properly?

I hope this was not a confusing example.

 

[theDBguy]

Hmm, not following you on this one.

To give you an example;
In Access, I have a table called 'Locations'. I create a query of this for the purposes of Exporting it to Excel. In Excel, I also have a sheet named Locations. BUT, while both the table in Access and Sheet in Excel represent the same data..the Access field names I am exporting to Excel have different Field names than the ones in Excel. How does export handle this? How does it even know to replace that data properly? (since it can't compare Field names?) I understand that it would know the right Sheet..but how about comparing values properly?

I hope this was not a confusing example.

Hi. Let me throw the question back at you. How are you doing the export now? How were you handling the difference in the filed names currently?

 

[eddie84]

Hi. Let me throw the question back at you. How are you doing the export now? How were you handling the difference in the filed names currently?

Thank you for asking that. I see from looking at the FE that it was done via DAO.recordset . Which I know nothing about.

 

[eddie84]

Hi. Let me throw the question back at you. How are you doing the export now? How were you handling the difference in the filed names currently?

I do not see anything about fields in the code using DAO.recordset.

 

[theDBguy]

I do not see anything about fields in the code using DAO.recordset.

Hi. If you need help with the DAO code, we'll need to see it. If you want to try creating a query with column names matching the Excel sheet headers and using the TransferSpreadsheet method, try using aliases. For example,

SELECT TableFieldName AS ExcelColumnName FROM TableName

Hope that helps...

 

[eddie84]

Hi. If you need help with the DAO code, we'll need to see it. If you want to try creating a query with column names matching the Excel sheet headers and using the TransferSpreadsheet method, try using aliases. For example,

SELECT TableFieldName AS ExcelColumnName FROM TableName

Hope that helps...

So, I do see a reference to a range: I'm guessing that in application, the field names don't really matter as long as you are referring to the specific sheet and it knows where to start?

"
Copy Replacement Worksheet Records
With objShtLoc
.Range("A3").CopyFromRecordset rsRecs
objWkb.Worksheets(stShtLoc).Visible = False
End With"

 

[theDBguy]

So, I do see a reference to a range: I'm guessing that in application, the field names don't really matter as long as you are referring to the specific sheet and it knows where to start?

"
Copy Replacement Worksheet Records
With objShtLoc
.Range("A3").CopyFromRecordset rsRecs
objWkb.Worksheets(stShtLoc).Visible = False
End With"

Yes. If, we say, the first two rows contain the headers, then that code is putting the data starting on row 3.

 

[eddie84]

Yes. If, we say, the first two rows contain the headers, then that code is putting the data starting on row 3.

That makes sense.

One point of confusion left.


My supervisor asked me to first write a function in Access to export a sorted query to Excel (the query has Locations, in one case), then create a button that will refresh the Excel lists.

This dao.recordset code is tied to a Updating Locations control/button.

What would the need even be to write a function to export first, when this dao.recordset takes as an argument the name of the query that is the source of rows that enable the Excel list to be updated?

 

[arnelgp]

can you not Link the table from Excel (Data -> Get and Transform Data).
you can refresh it within Excel without any part of coding in Access.

 

[Micron]

cross posted
https://www.accessforums.net/showthread.php?t=81429