Import WS and Replace Tab

A link to the previous question, as background information, for any interested party:

[ http://www.experts-exchange.com/questions/28706636/Exporting-a-WS-to-a-specified-Location.html ]

PS. The attached file, "D--Data-Data-Temp-Save-to-Export-and-Imp", should be downloaded, then renamed to:

"D--Data-Data-Temp-Save-to-Export-and-Imp.xlsm" (or any filename with a ".xlsm" extension)

[ http://filedb.experts-exchange.com/incoming/2015/08_w34/930515/D--Data-Data-Temp-Save-to-Export-and-Imp ]

Fanpages,  my system just started including the entire "path" when I try to upload a file.  What would you suggest in order to have it not include the path and simply use the name?

B.

Hi B,

It is not your fault this is happening, so please don't feel you are doing anything 'wrong'.

(There are a few threads providing feedback to the site's staff on this subject; one as recent as earlier this week.  I have participated within it, so I can keep up-to-date on developments).

To answer your question, though, what web browser/system/operating system are you using?

Whenever I attach/upload a file, all I see is the filename, not the fully-qualified "drive-folder-subfolder-filename-ext".  It looks like this may be a specific problem with a web browser, or perhaps an operating system.

PS. Text I have just posted in your previous question:

Answer to Question 1:  YES, I'm looking for a way to direct the save to a specific user chosen location (the Save As was a nice extra I hadn't thought of.... I'm going to also need to retrieve the file and import it later....so it might be good to simply save it with one particular name that the User cannot change).

I was going to offer a "Browse for..." routine at the top of the thread, but your reply to my query led me to believe that you needed a fixed folder location, not a variable location that the user of the workbook could select.

With the mention of a drop-down list, I thought you were progressing with that, so left the question to Ryan.

Now you have a, potentially, variable (not pre-determined) file location where the file is saved, this adds complication to the next part of your project.

Have you considered the possibility that a user may select an out-of-date file for import, rather than the file they have (just) exported (via the code provided in this question)?

I've got Win 7.0 and use Bing.  This is a "new phenomena".

B.

Bing is a "web search engine" (or, to Microsoft, a "decision engine"), not a web browser.

To view this site (& any other site, Bing.com, included) are you using Microsoft Internet Explorer, or Mozilla Firefox, or Google Chrome, or Safari, or something like that?

(The issue is not new to the site; it has been around for over a year!)

I meant to say.... "its a new phenomena" on my system.  Previously, when I uploaded a file, it only took the file and not the path.

B.

OK...here's the latest updated file.

B.
D--Import.xlsm

fanpages,

YOUR STATEMENT:
Have you considered the possibility that a user may select an out-of-date file for import, rather than the file they have (just) exported (via the code provided in this question)?

YES.  The routine is being used to simply back up the DB while a new Model is being built.  The User keeps the DB updated by continuously saving it to the same location.  I do however, like your idea of adding Date/Time, if "On Import" that file information can be stripped in the return of the Tab Name.  The problem is that I have macros that go against this Tab.  Change the name and the Macro will break.

Did you get my update from above?  Are you working on the Import Macro?

Thank you,

B.

try this...

Sub RoundedRectangle8_Click()
    On Error GoTo Err
    
    Dim src As String
    src = Application.GetOpenFilename("Excel,*.xls", , "Select file")
    If src = "False" Then Exit Sub
    
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws2 As Worksheet
    Dim w As Worksheet
    Dim targetSN As String
    targetSN = "Industry_DB"
    
    Set wb1 = ActiveWorkbook
    
    Set wb2 = Workbooks.Open(src)
    Set ws2 = wb2.Worksheets("Industry_DB")

    ActiveWindow.Visible = False
    
    Application.DisplayAlerts = False
    For Each w In wb1.Worksheets
        If w.Name = targetSN Then
            w.Delete
            Exit For
        End If
    Next
    Application.DisplayAlerts = True
    
    wb1.Worksheets.add After:=wb1.Worksheets(wb1.Worksheets.Count)
    ws2.Cells.Copy wb1.Sheets(wb1.Worksheets.Count).Cells

    wb1.Worksheets(wb1.Worksheets.Count).Name = targetSN
    wb1.Worksheets(1).Select
    
    'Windows(wb2.Name).Visible = True
    Application.DisplayAlerts = False
    wb2.Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    wb1.Save
    
    Set wb1 = Nothing
    Set wb2 = Nothing
    Exit Sub
Err:
    MsgBox "Error while importing worksheet", vbCritical, "Error"
End Sub

[b]Pls make your backups before run the scripts, and you can customize the logic accordingly.[/b]

Select all Open in new window

D--Import-b.xlsm

Ryan, "brilliant"!  Let me test it a few times this morning.  I see you live in Singapore.  I may be in your neck of the woods in the next 30 days.

B.

OK... one problem.  What if the Industry_DB is a hidden tab?  It happens to actually be "hidden" in my WB so when this Import/Export happens, it needs to search for the Tab Name (WS) regardless of Hidden or visible.  

Make sense?

B.

ok, try:

Sub RoundedRectangle8_Click()
    On Error GoTo Err
   
    Dim src As String
    src = Application.GetOpenFilename("Excel,*.xls", , "Select file")
    If src = "False" Then Exit Sub
   
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws2 As Worksheet
    Dim w As Worksheet
    Dim targetSN As String
    targetSN = "Industry_DB"
   
    Set wb1 = ActiveWorkbook
   
    Set wb2 = Workbooks.Open(src)
    Set ws2 = wb2.Worksheets("Industry_DB")

    ActiveWindow.Visible = False
   
    Application.DisplayAlerts = False
    For Each w In wb1.Worksheets
        If w.Name = targetSN Then
            w.Delete
            Exit For
        End If
    Next
    Application.DisplayAlerts = True
   
    wb1.Worksheets.add After:=wb1.Worksheets(wb1.Worksheets.Count)
    ws2.Cells.Copy wb1.Sheets(wb1.Worksheets.Count).Cells

    wb1.Worksheets(wb1.Worksheets.Count).Name = targetSN
   
    'Hide worksheet
    wb1.Worksheets(targetSN).Select
    ActiveWindow.SelectedSheets.Visible = False
   
    wb1.Worksheets(1).Select
   
    'Windows(wb2.Name).Visible = True
    Application.DisplayAlerts = False
    wb2.Close SaveChanges:=False
    Application.DisplayAlerts = True
   
    wb1.Save
   
    Set wb1 = Nothing
    Set wb2 = Nothing
    Exit Sub
Err:
    MsgBox "Error while importing worksheet", vbCritical, "Error"
End Sub

>> I may be in your neck of the woods in the next 30 days.
Welcome to Singapore! : )
D--Import-c.xlsm

Do I also need to modify the Export Sub?

B.

In either Importing or Exporting....the DB WS Tab may or may not be visible.

B.

>> Do I also need to modify the Export Sub?

I think we no need to do so.

Even when I run your code,

I get an error on this line:  Worksheets("Industry_DB").Copy

Copy Method of this Worksheet Class Failed.

B.

On Exporting......

B.

ok, for export, try this instead. it seems that the worksheet need to be unhidden before it can be copied over...

Sub RoundedRectangle1_Click()
    Dim Path As String
    Dim fileName As String
    
    Path = BrowseForFolder()
    If Path = "" Then Exit Sub
    
    'Path = IIf(Right(Cells(8, 3), 1) <> "\", Cells(8, 3) & "\", Cells(8, 3))
    Path = IIf(Right(Path, 1) <> "\", Path & "\", Path)
    'try customize this accordingly
    fileName = "Industry_DB" & Format(Now(), "YYYYMMDD HHMMSS") & ".xls"
    
    ThisWorkbook.Worksheets("Industry_DB").Visible = True
    Worksheets("Industry_DB").Copy
    ActiveWorkbook.SaveAs Path & fileName, xlExcel8
    ThisWorkbook.Worksheets("Industry_DB").Visible = False
        
    MsgBox Path & fileName & " saved successfully.", vbInformation, "Completed"
    
End Sub

Select all Open in new window

D--Import-d.xlsm

Ryan,

Greetings!  I've been testing the code and have a question.  When I run it standalone (your actual code), it works great.  When I import it and run it in my actual WB, I get a Compatibility Checker notice (upon Export).  Also, if I save it (export) as a XLS file, it seems to work fine.  But changing it to an XLSM file (in the code), I get an error.

1.) Is there any way to sepress the "compatibility checker" and Defined names option that continues to pop up?
and
2.) Is there a way to save these files as XLSM instead of XLS (because that would resolve #1)?

Thank you,

B.

There is something "amiss" when I use this code in my workbook.  

So on the Export function....it does a compatibility check and asks if I want it to recalculate the macros/names/formulas when I open it.  I can say Yes or No and it will save it to the right place.

BUT

When I Import the same XLS sheet, it appears to create another Tab and runs and runs..then returns the message (after about a minute), Error while Importing.

B.

Here is something else weird but probably related that happened.  The Export/Import capabilities changed the Sheet numbers in the Excel Objects.  In my pre-version, Industry_DB is Sheet 35. In the post version (after importing it back in, it is Sheet 14.  This is some result of the copy/paste or export/import..... not sure.  But again, may be part of the settings in the code.

B.

Ryan,

I've been playing with this now for about 2 hours and I must say... your code...."as is" is flawless.  I'm going to close this out.  Unfortunately, in it's current condition, I cannot incorporate it because it continues to have a problem (even when I directly copy it over into another Module) with interference with the way I have something set up in my system.  Any trouble shooting ideas would be most appreciated or if there is a way to better isolate the routine from interfering with other macros, that would be helpful.  You can see from above that I have something else going on.  I think the fact that it does a compatibility check is a clue as well as the fact that it apparently asks if it should rerun macros, etc. when restored is also a clue.  None of that happens when I run your code.  The next thing I'm going to try is to change your file to xlsm.  One other thing I have noticed; when I save the DB, it still appears in the VBA Editor although it is closed.  Could that be also interfering with things as I go to import?  Is there a line of code that can close the DB out completely when exporting it?

Much thanks for all the assistance here.

B.

Ryan,

I tested saving the file as an XLSM and XLSX file and neither worked.  Could that be what's causing the problem?  We are saving and importing an XLS WS file?

B.

Hi B,

Just catching up on your comments.

If you change the file extension to ".xlsm":

fileName = "Industry_DB" & Format(Now(), "YYYYMMDD HHMMSS") & ".xlsm"

You will also need to change this line...

From:
ActiveWorkbook.SaveAs Path & fileName, xlExcel8

To:
ActiveWorkbook.SaveAs Filename:=Path & fileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

or, simply, as previously stated:

ActiveWorkbook.SaveAs Path & fileName, xlOpenXMLWorkbookMacroEnabled

If changing to ".xlsx":

ActiveWorkbook.SaveAs Path & fileName, xlOpenXMLWorkbook

Fanfares, thank you for the comment. The master workbook is an XLSM file. I'm assuming that it's best to use the same format. I'll give it a try.

B

Save the fanfares until the code is working! :)

Never trust Siri!

B.

Fanpages,

We are getting close to having this work.  Your recommendation worked on the Export.  On the Import, I got a notification that the file being imported "This workbook contains links to other data sources."   Here's the thing, when the WS is copied and pasted as an export it also changes the Sheet number.  In other words when I import it back my Sheet# has changed from Sheet35 to Sheet 14.  And although I get an error in import, it still posts it.  When I did it a second time, it changed the Sheet to 16.  Not sure what is going on....but that is what is happening.

B.

Wait....there is one more thing.  In the original DB that is Exported....there is a ActiveX button that contains code.  That could be what is causing the problem.  When I do get the Import back in, after the issues listed above, it doesn't have that control any longer in the WS.

B.

Fanpages and Ryan,

I think I have isolated the problem.  Here is what happens.  When you export the DB, it creates a specific file (with timedate stamp).  So when you Import it, it erases the current DB that's in the WB and copies the DB that was exported.  When it does this, it creates a new Sheet that has a new number.

Can you take a look?  Here is my test copy.  You will see that I changed the DB name to UseCase_DB but that should have no impact.  The only other consideration is that I have a ActiveX Control in the DB that gets dropped when the export takes place....so also when you import it back in, the Control is gone.

Thanks for your help in advance,

B.
D--Import-etest.xlsm

What I need is a exact copy to be pulled back in, replacing the current DB.  And keep the ActiveX control.

Thanks,

B.

@Bright01,

Am I right to say these amendments?

When Export:

1. Export the worksheet "UseCase_DB" instead of "Industry_DB"
2. Export it as a xlsm instead.
3. Avoid the prompt of compatibility issues

When Import:

1. Check the validity of the import source file. Got valid worksheet named "UseCase_DB" or "Industry_DB"
2. Prompt user whether to overwrite the worksheet or not. If yes then proceed else abort the process.

Did I miss out anything?

Ryan, you are absolutely correct, except when it comes back into the imported work book it needs to keep the same sheet number and name. Right now for some reason the sheet number changes.  It also needs to protect and preserve the ActiveX control which is part of the worksheet. If that is not possible let me know and I will pull that active X control out of the use case DB.

B

ok, you can customize this (with credits to Fanpages's posts that provided some useful codes):

Sub ExportUseCaseDB_Click()
    Dim Path As String
    Dim fileName As String
    Dim b As Boolean
    Dim srcWS As String
    srcWS = "UseCase_DB"
    
    Path = BrowseForFolder()
    If Path = "" Then Exit Sub
    
    
    'Path = IIf(Right(Cells(8, 3), 1) <> "\", Cells(8, 3) & "\", Cells(8, 3))
    Path = IIf(Right(Path, 1) <> "\", Path & "\", Path)
    'try customize this accordingly
    fileName = srcWS & Format(Now(), "YYYYMMDD HHMMSS") & ".xlsm"
    
    b = ThisWorkbook.Worksheets(srcWS).Visible
    ThisWorkbook.Worksheets(srcWS).Visible = True
    Worksheets(srcWS).Copy
    ActiveWorkbook.SaveAs Path & fileName, xlOpenXMLWorkbookMacroEnabled
    ThisWorkbook.Worksheets(srcWS).Visible = b
        
    MsgBox Path & fileName & " saved successfully.", vbInformation, "Completed"
    
End Sub

Sub ImportUseCaseDB_Click()
    On Error GoTo Err
    
    Dim src As String
    Dim targetSN As String
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws2 As Worksheet
    Dim w As Worksheet
    Dim idx As Integer
    
    targetSN = "UseCase_DB"
    
    src = Application.GetOpenFilename("Excel (Macro Enabled Workbook),*.xlsm", , "Select file")
    'src = Application.GetOpenFilename("Excel,*.xls", , "Select file")
    If src = "False" Then Exit Sub
    
    Set wb1 = ActiveWorkbook
    
    Set wb2 = Workbooks.Open(src)
    Set ws2 = wb2.Worksheets(targetSN)
    
    ActiveWindow.Visible = False
    If MsgBox("Are you sure want to continue?", vbExclamation + vbYesNo, "Overwrite?") = vbNo Then GoTo Q
    
    Application.DisplayAlerts = False
    idx = 1
    For Each w In wb1.Worksheets
        If w.Name = targetSN Then
            w.Delete
            Exit For
        End If
        idx = idx + 1
    Next
    Application.DisplayAlerts = True
    
    'wb1.Worksheets.add After:=wb1.Worksheets(wb1.Worksheets.Count)
    'ws2.Cells.Copy wb1.Sheets(wb1.Worksheets.Count).Cells

    wb1.Worksheets.add Before:=wb1.Worksheets(idx)
    ws2.Cells.Copy wb1.Sheets(idx).Cells

    wb1.Worksheets(idx).Name = targetSN
    
    'Hide worksheet
    wb1.Worksheets(targetSN).Select
    ActiveWindow.SelectedSheets.Visible = False
    
    wb1.Worksheets(1).Select
    
    'Windows(wb2.Name).Visible = True
    Application.DisplayAlerts = False
    wb2.Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    wb1.Save
    
    Set wb1 = Nothing
    Set wb2 = Nothing
    Exit Sub
Err:
    MsgBox "Error while importing worksheet", vbCritical, "Error"
    'Exit Sub
Q:
    On Error Resume Next
    Set w = Nothing
    Set ws2 = Nothing
    'wb1.Close
    wb2.Close False
    'Set wb1 = Nothing
    Set wb2 = Nothing
End Sub

Select all Open in new window

D--Import-etest-b.xlsm

^ No need to credit me, Ryan; but thanks all the same! :)

Ryan and Fanpages,

I hate to keep talking about this....but I still have one problem when testing.  When I "Import" the UseCase_DB, I get a message to Update Links.  There should be no links in this simple copy/paste for backup.  Is there a way to suppress this requirement?  I can go into the stored (exported file) and cut it off, but I'll be doing that, I think, every time I save or export.  Also, if I choose not to update, I get another error.

B.

If the worksheet being "exported" (saved within an external workbook) refers to a cell, a range of cells, a worksheet, or a named range within the original workbook, then there will be a link back to the original workbook.

In order to resolve this, the "exported" worksheet will need to replace any in-cell formulae with the explicit values (i.e. the result of any formulae calculations) during the "export" process, by selecting the entire contents of the worksheet (or just the cells that contain formulae), copying to the clipboard, then pasting "as values" back over the same cells.

Continued...

[ http://www.experts-exchange.com/questions/28708070/Changing-and-Deleting-Links-in-an-Excel-Spreadsheet.html ]

Ryan and Fanpages,

I have removed all of the links.  But here's where I have the two issues.

1.) It continues to drop the ActiveX control when I export the WS UseCase_DB.

and more disturbing,

2.) When I import it, it changes the Sheet number (in the Developers Tab) so other Macros are thrown off.  I need the macro to simply replace the existing sheet with the one saved.

Thanks!

do you have a working sample can uploaded here? With some minimal fake data...

I can't really imagine effectively without the real sample.

2.) When I import it, it changes the Sheet number (in the Developers Tab) so other Macros are thrown off.  I need the macro to simply replace the existing sheet with the one saved.

Do you mean the VB Project Codename ("Sheet35") rather than the visible "Tab" worksheet name [UseCase_DB], for instance?  The Codename is shown as "(Name)" at the top of a Worksheet's Properties window within the Visual Basic Project Interface, whereas a Worksheet's "Tab" name is shown as "Name" (listed lower down the list):


If so, then I would suggest not using the Codename within any Visual Basic for Applications code, but using a reference to the Worksheet name instead.

That is,...

Worksheets("UseCase_DB")....

Rather than

[Sheet35]...


The numeric suffixes are sequential based on the Workbook in which the worksheet is created; if it was "Sheet35" in one workbook, then exported, & imported elsewhere, unless 34 other worksheets have resided within the destination workbook, the internal Codename is not going to be consistent.

Here is a doctored up sample of what I'm struggling with.  If you Export.... no problem.  BUT, when you Import, it asks you to update the links (none of which are valid), then it may or may not give you a resource error. Even if it does, if you go to the Developer Tab and look at the Sheets, you will see that the UseCase_DB has changed from Sheet 35 to Sheet 2.

Hope this helps.

B.
Test-File-for-Copy-of-DBv5.xlsm

Ryan and Fanpages,

You guys are great!  Thank you very much!  I'm going to be testing this today but I did integrate it into my main WB and it works.  The only thing that is still a problem is that it continues to try to update links.  So just so I know..... if I can eliminate all of the formulas and references within the WS (UseCase_DB) then save it, I should have no Links to update.  Is that correct?  If so, I'll try that out.

Again, you guys have done a wonderful job here.  If I make it to Singapore in the fall.... I'm going to take you out to dinner.  Fanpages, not sure where you are.... but I definitely owe you a meal also.

Much thanks,

B.

Your appreciation of my input is noted, B., but Ryan took on most of this task.

Thank you in any respect.

Regarding 'rogue' links; please also look within the Name Manager for any entries that may be pointing to external workbooks... & remove them.


Good luck! :)

a side note.... I think [ fanpages ] is at UK. I wish to be there someday in future too : )

glad that we both could make some helps here cheers.

TERRIFIC JOB!  This was one of the longest interactions I have had with EE and was a delight to work with both of you.  I learned a bunch of new things about Excel and feel as if I made two friends in the process.  You guys are very talented and have an attitude that is all "can do"!   I now have an Export/Import Program and can actually figure out (at least a little) about how it works.

Thank you very very much for the hard work and I hope we can interact more in the future.  Fanpages, I'm on the way to Paris in two weeks and may get to London.  It's doubtful but if it materializes, I'll send you a note.

Ryan, I'll be in Australia in 4 weeks but not sure if I can make Singapore.  Will also let you know.

As they say in London, "Cheers"!

B.