Export or SaveCopyAs in Excel?

[shelby55]

Hello

I am using Excel 2003.

I have an Excel workbook that is importing data from an Access database. It's working fine. What currently happens is that this database imports the data on open but doesn't save it so essentially it is a "shell" workbook. The Access data is updated daily via an automated process.

One worksheet of the workbook is aggregated data by date which is the data source of the graphs and cells of the other 3 worksheets.

On the first page there is a reference cell that was created using data validation and named range. In the Data Validation dialogue setting tabname range it is defined as "=OFFSET('Raw Data'!$A$3,0,0,COUNTA('Raw Data'!$A:$A)-2,1)" . The summary page has current date/7 days ago and same time last year so when the reference date is changed it affects what shows up on all the other worksheets.

To avoid the users' version of this accessing the Access database every time (because it will be accessing across servers) I want to have a version saved with the raw data in it but without users being able to edit any of the cells' data. Ideally I'd like the raw data worksheet to be invisible. The only cell that should be able to be used is the reference cell so the user can look at different dates and the summary/graphs that result from that date but there is nothing to enter, just select from the pull down list as described above.

Any help greatly appreciated.

 

[AnotherHiggins]

Let's break down your requests:

I want to have a version saved with the raw data in it...

I'd save archive copies with the dates in the name of the file.

If I understand you correctly, you have 1 week of data in the file, year-over-year. So maybe June 28 - July 4 2010 and either the same dates or same week number for 2009. You can get the dates in VBA with a formula. The exact formula will depend on how you're pulling the dates (actual dates, week number, etc) as well as where those dates are stored in your data table.

But here's an example: Let's say that your dates are in Column A starting in Row 3 (I'm basing that assumption on your named range) and you're looking at week number 27 (6/28/2010-7/4/2010 & 6/29/09-7/5/2010).

intWeekNmbr = Application.WorksheetFunction.WeekNum(Range("A3"), 2)
intCurrentYear = Format(Application.WorksheetFunction.Max(Range("A:A")), "yyyy")

We can incorporate that into our archived file name:

strArchiveLocation = "C:\Temp\"
strArchiveName = "ArchiveFile_" & intCurrentYear & "-Week" & intWeekNmbr & ".xls"

activeworkbook.SaveAs Filename:=strArchiveLocation & strArchiveName

...but without users being able to edit any of the cells' data.... Ideally I'd like the raw data worksheet to be invisible.

Excel is not extremely secure. But assuming the data isn't a big secret - which I think is a safe assumption since you're sharing charts of the data - then you can accomplish your goal with:

Sheets("Raw Data").Visible = xlVeryHidden

Setting the sheet to xlVeryHidden will require someone to use VBA to unhide the sheet. The sheet will not be available if you go to Format > Sheet > Unhide.

To avoid the users' version of this accessing the Access database every time

You can delete all External Connections in the workbook. I'm using Excel 2007 so I could use this:

For Each cnctn In ActiveWorkbook.Connections
    cnctn.Delete
Next cnctn

I'll have to test to be sure, but I think in 2003 you could use:

For Each qt In ActiveSheet.QueryTables
    qt.Delete
Next qt

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[shelby55]

Hi John

Thanks very much. I don't know if it matters or not but I actually have all last year (Apr 2009 to Mar 2010) and also April 2010 to yesterday data with new days (not weeks) adding daily.

In the rawdata sheet I have all the dates there when I open up the shell. So I just want to export/copy that data (with the other worksheets) to a new version of the file but not have a link to the Access database. The original workbook has the link to Access to populate the "raw data" worksheet and I don't want that link on the new workbook, just the data.

So will what you've suggested still work for that? Thanks.

 

[AnotherHiggins]

Yes. The only thing you'd have to change is the dates, which was a guess on my part anyway.

The point is you can easily determine the date - either by using the current date

dteSaveDate = datevalue(Now())

or by finding the maximum date in the data set

dteSaveDate = Application.WorksheetFunction.Max(Range("A:A"))

(or whatever column(s) contain your date)

and then incorporate that date into a unique, archivable file name.

The last bit of code in my first post is what gets rid of the external link to your database, so the archived file no longer contains the link, just the data that was pulled into Excel.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[shelby55]

Thanks John! I'll give this a whirl and let you know how I do.

 

[shelby55]

Hi John

I'm still not understanding something and that is the code to save as or export?

Maybe I'm not being clear but I want the workbook to be saved with the data in it and clear links to the database. I'm not sure what your code about the dates is related to...I believe it's just so I know what version is being created?

In fact, this will be a daily update but the workbook can just be saved with a different name that keeps overwriting itself.

Thanks.

 

[shelby55]

Hi

Please keep in mind when I ask this question that I'm not a VBA pro because it might sound stupid: what event should I be placing the code in.

Also don't I want to be exporting this data or something because if I am saving this file then when i open this file it also tries to save as the name in the vba code which it can't because that is the workbook I'm in.

Thanks.

 

[shelby55]

Hi

I tried some of the code and the hidden sheets etc. all applied to the CURRENT worksheet, not the copy I'm creating.

I really hope someone can help here...thanks.