Export to Excel, Import to Access Automatically without linking???????

[djrickel]

I need to automate a process of exporting data to Excel, allowing a spreadsheet no-so-affectionately called The Monster, to do its magic, then import the data back to Access to be used in an already-formatted report. I'm smart enough to recognize that this requires VBA but not at all in order to code it out so that it functions. Tried and wasted over a week so I'm begging some kind-hearted whiz to help me.

NO WE ARE NOT INTERESTED IN LINKING ACCESS TO THE MONSTER. Tried and it crashes both Access and Excel every time. It's not called The Monster for nothing. This is what I've done and what I need help with...(don't laugh)...

1. In Access, have a query that creates a recordset of variables.
2. Have a macro called mcrExportToExcel that uses the query to export those variables to C:\FS\DataFromAccess.xls
2a.(killing the existing file first, since it will always exist).
2b.I suppose I should instead run DoCmd.TransferSpreadsheet to DataFromAccess.xls in A1:K1...I'm open to your expertise!

3. Linked those variables to The Monster in C:\FS\Plan1.xls.

* Need a way to quietly first open C:\FS\DataFromAccess.xls, then C:\FS\Plan1.xls, without the end-user knowing it, so that the links can update and Plan1.xls can do it's beautiful magic.

* In Plan1.xls, need to automatically clear then reapply a filter that hides "." in A1:A70. The need to clear & refilter will need to occur each time the file is opened.

* Need to quietly save and close both Excel files without any messages -- remember the user doesn't know this is all happening.

4. Run DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblPlan1", "C:\FS\Plan1.xls", , "Plan 1!A1:AE71" to import data back to Access into a table called tblPlan1.

5. Use an already-formatted Access report named rptPlan1 to preview or print..

Is there a kind soul who will have pity on someone who is trying very hard to self-learn and is exhausted from wasting a week on this already? Please don't tell me this needs VBA--I know that. I need code as I have tried and failed and have little hair left to pull out. PLEASE DON'T LET ME GO BALD!!!!!

 

[SkipVought]

hi,

Need a way to quietly first open C:\FS\DataFromAccess.xls, then C:\FS\Plan1.xls, without the end-user knowing it, so that the links can update and Plan1.xls can do it's beautiful magic.

Here's what you can do, [highlight #FCE94F]IN Plan1[/highlight].xls (it appears to be an OLD 97-2003 workbook)

***This is a ONE TIME task [highlight #FCE94F]in Plan1[/highlight]***: Use MS Query (Data > Query or something like that, I forget the 2003 options) to grab the data from DataFromAccess.xls. You can check a box in the External Data Properties to REFRESH the query each time [highlight #FCE94F]the workbook[/highlight] is opened.

Thats all it takes! No VBA required!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[djrickel]

Hmmm. Never used MS Query in Excel but would like to! I'm using Excel 2013 to manage Plan1.xls and I don't even see an option for query anywhere. The Help Topics talking about something called Power BI when I search for query.

Is there a way to have a macro run automatically when Plan1 is opened that will do the filter? If so, this goes back to how to open/update without the user knowing it?

Thanks for your help.

 

[SkipVought]

I found a 2003 reference:

You want to do this on the sheet in Plan1 or Plan2 that will receove the data from [highlight #FCE94F]Access/Excel workbook[/highlight]
Data > Import External Data > New Database Query...

Then a Choose Data Source window pops up.

UNCHECK the Use Query Wizard box at the bottom

In the Database Tab choose Excel Files*

Drill down to your [highlight #FCE94F]Access/Excel workbook[/highlight]

The Add Tables window will pop up. Hit the OPTIONS button and CHECK ALL BOXES. (One time operation)

Select the Sheet Name that conteins your data. The GUI is much like MS Access. Format your query.

Data > Return Data to Microsoft Office Excel.

1) Select the sheet/cell to receive the data
2) Hit the PROPERTIES button and in External Data Range Properties, select [highlight #FCE94F]Refresh data when opening the file[/highlight] in the Refresh control section.

OK out to return data to your sheet.

Finé!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[djrickel]

When I drill down to DataFromAccess.xls, the ADD TABLE box comes up but there are no tables to display. I'm assuming it thinks each worksheet is a table? There is only the one called qryExportToExcel but it doesn't even show up. Any idea why?

 

[SkipVought]

but there are no tables to display.

Did you...
[tt]
The Add Tables window will pop up. [highlight #8AE234]Hit the OPTIONS button and CHECK ALL BOXES[/highlight]. (One time operation)
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[djrickel]

Well it certainly helps when I follow alllllll of the instructions to a tee, doesn't it?

So, if I understand correctly, doing the query thing above allows Plan1.xls to get the variables from DataFromAccess.xls so that I don't have to open either anymore???????? Plan!.xls will update without being opened so that I can import it back to Access? If so then a bit MWAHHHHHHHH to you!!! (that's a kiss )

 

[SkipVought]

Plan!.xls will update [highlight #FCE94F]without being opened[/highlight]

2) Hit the PROPERTIES button and in External Data Range Properties, select [highlight #FCE94F]Refresh data when opening the file[/highlight] in the Refresh control section.

You seem to have a habit of not reading carefully!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

...also as you initially stated...

[highlight #FCE94F]allowing a spreadsheet ... to do its magic[/highlight], then import the data back to Access

..and it would seem to me that "doing its magic" would involve the workbook being [highlight #FCE94F]open[/highlight] and [highlight #FCE94F]running[/highlight] some sort of report.

YES?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[djrickel]

Hey!!! I'm taking my MWAHHHH back. In my defense, I have a co-worker who thinks I care about her life. I ignore her but cannot concentrate so my apologies for appearing to be such an airhead.

2) Hit the PROPERTIES button and in External Data Range Properties, select Refresh data when opening the file in the Refresh control section.

In the query, I cannot find a PROPERTIES button but there is an Auto-Query button that is selected. There is no Refresh control section in the worksheet properties. Am I looking in the wrong place?

Also, the idea from the Access end-user's viewpoint is to have the export/updating/import done seamlessly, so that's why I asked if the updating would be done without opening Plan1.xls before it is imported.

I hope I don't sound stupid or ungrateful because I truly do appreciate your help. In fact, you are BRILLIANT and being very patient with me. Thanks!

 

[SkipVought]

In the query, I cannot find a PROPERTIES button

It is in the process of Edit > Return data to Excel INITIALLY, when the query is being added to the sheet THE FIRST TIME, that you will encounter in the Import Data window the Properties button.

If you have already added the QueryTable to your sheet, then Right-Click IN the resultset, Select Import External Data > Data Range Properties OR Data Range Properties.

It is in Data Range Properties that you can select when the refresh is to occur.

As to your one click vision, lets say, for the purpose of argument, that the exported data gets into the Plan1 & Plan2 workbooks by some automatic means. Is there not anything else that needs to happen within each workbook, WITH THE DATA FROM DataFromAccess.xls, before Access grabs the data? And if so, how might that happen?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[djrickel]

I'm guessing you are referring to linking the variables in DataFromAccess.xls to the appropriate cells in Plan1.xls--which has already been done. The issue is that it won't update until I open the file, save and close it. Then the import pulls in the right stuff.

 

[SkipVought]

RIGHT!

I'm guessing that when you say, " [highlight #FCE94F]linking[/highlight] the variables in DataFromAccess.xls to the appropriate cells in Plan1.xls" that this means that when the DATA (these are not variables, but DATA) is imported into a sheet in Plan1 & Plan2, that some [highlight #FCE94F]formulas[/highlight], assuming that Calculation is set to AUTOMATIC, will calculate new values. Yes, [highlight #8AE234]this calculation[/highlight] can only happen with the workbook open.

And as far as Access is concerned, [highlight #8AE234]this[/highlight] is an asynchronous process: it ain't over until its over, however long that takes: 1 second, 1 minute, 1 hour? I'd guess with a "Monster" workbook, its a [highlight #AD7FA8]go-geta-cupa-coffee[/highlight] process time.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[djrickel]

I wish. I could use a nice big cupa coffee.

The Monster updates instantly, probably around a second or two. Still need a way to open Excel from Access, allowing Plan1 to update, then save and close it without anyone noticing. I'm using this...

Private Sub Command514_Click()
Dim appExcel As Object
Dim workBook As Object
Dim workSheet As Object
' Open an existing spreadsheet
Set appExcel = GetObject("C:\FS\Plan1.xls")
' Do NOT Show spreadsheet on screen
appExcel.Application.Visible = False
appExcel.Parent.Windows(1).Visible = False

' Turn prompting OFF and save the sheet with original name
appExcel.Application.RefreshAll
appExcel.Application.DisplayAlerts = False
appExcel.Application.Save
appExcel.Application.DisplayAlerts = False
appExcel.Quit
' Release objects
Set workSheet = Nothing
Set workBook = Nothing
Set appExcel = Nothing

End Sub

The first problem is that it errors out on DisplayAlerts. Next it closes the Plan1.xls but keeps Excel open. What am I doing wrong?

 

[SkipVought]

give this a try

Private Sub Command514_Click()
    Dim appExcel As Object
    
    ' Open an existing WORKBOOK
    With GetObject("C:\FS\Plan1.xls")
        
        'set the Excel application object
        Set appExcel = .Parent
        
        ' Do NOT Show the application on screen
        appExcel.Visible = False
        appExcel.Windows(1).Visible = False
        
        'refresh all in the WORKBOOK
        .RefreshAll
        
        ' Turn prompting OFF and save the WORKBOOK with original name
        appExcel.DisplayAlerts = False
        .Save
    End With
    
    appExcel.DisplayAlerts = True
    appExcel.Quit
    
    ' Release object
    Set appExcel = Nothing
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[djrickel]

UH OH.

I used your recommended code but it hung up Access and Excel.

Now I'm unable to visibly open Plan1.xls at all. Excel opens with the toolbars on top and a blank screen below. I have to go to the Task Manager to end the application. Then Access will function.

Helllpppppppp!!!!

 

[SkipVought]

you code...

' Do NOT Show spreadsheet on screen

Well you had an application object and a workbook object.

But you were using the application to refer to the workbook

Set appExcel = GetObject("C:\FS\Plan1.xls")

The GetObject function ASSUMES that that thing is active ALREADY!

Note
Use the GetObject function when there is a current instance of the object or if you want to create the object with a file already loaded. If there is no current instance, and you don't want the object started with a file loaded, use the CreateObject function.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[djrickel]

Ok, I get it, but if I open Excel and try to open Plan1.xls I still have the same problem, which is the file won't visibly open anymore. Now what?

 

[SkipVought]

You must use the GetObject("Excel.Application") to assign the Excel Application Object and the use the Workbook.Open method to open the workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[djrickel]

I'm sorry but I'm totally frazzled and confused. Is this what you're talking about?

' Open an existing WORKBOOK
Workbook.Open ("C:\FS\Plan1.xls")

'set the Excel application object
GetObject("Excel.Application")
Set appExcel = .Parent

' Do NOT Show the application on screen
appExcel.Visible = False
appExcel.Windows(1).Visible = False

'refresh all in the WORKBOOK
.RefreshAll

' Turn prompting OFF and save the WORKBOOK with original name
appExcel.DisplayAlerts = False
.Save
End With

appExcel.DisplayAlerts = True
appExcel.Quit

' Release object
Set appExcel = Nothing
End Sub Private Sub Command514_Click()
Dim appExcel As Object

' Open an existing WORKBOOK
With GetObject("C:\FS\Plan1.xls")

'set the Excel application object
Set appExcel = .Parent

' Do NOT Show the application on screen
appExcel.Visible = False
appExcel.Windows(1).Visible = False

'refresh all in the WORKBOOK
.RefreshAll

' Turn prompting OFF and save the WORKBOOK with original name
appExcel.DisplayAlerts = False
.Save
End With

appExcel.DisplayAlerts = True
appExcel.Quit

' Release object
Set appExcel = Nothing
End Sub