Exporting a WS to a specified Location

EE Pros,

I am looking for a Macro that can export a specific WS to a specific location on my harddrive.

Attached is a mockup of the WS and the WS to export (as copy).

Thank you in advance,

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
[ http://filedb.experts-exchange.com/incoming/2015/08_w34/930215/D--Data-Data-Temp-Industry-DB-Export.xls ]

Redirects to...

[ http://www.experts-exchange.com/noSuchFile.jsp ]

"Not Found

The page you're looking for cannot be found"

UPDATE: Now able to be accessed.
UPDATE #2: Actual filename is "D--Data-Data-Temp-Industry-DB-Export.xlsm" <- Note the "m" suffix as the filename is truncated at 40 characters.
[ fanpages ]IT Services ConsultantCommented:

"I need a macro that when executed, exports the Industry_DB Tab/Worksheet (as a copy) to a specified Location on a hard drive by browsing (drop down box)"

Do you mean a drop-down combo-box in cell [C8] or, perhaps, a file "Save As" dialog box (shown over the top of the worksheet)?

Also, what is happening at cell [C16] (where a third arrow points to)?
Bright01Author Commented:
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).

Answer to Question 2:  The arrow points to the Tab, not a cell.  It's the DB Tab that I'm wanting to export.

Hope this clears up your questions.

and Thank you!

Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can try this example.

to create a dropdown in Excel's cell, you need to create a "Name" and then put the data validation in it.

create a name
Data validation
Define and use names in formulas
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
pls note the attached sample file name is:


try customize accordingly.
Bright01Author Commented:

Thanks for the reply but it's not at all what I was looking for or asked for assistance with.  

I am looking for and need a macro that provides the ability to export a particular Worksheet in a Workbook to a particular place on someone's harddrive.  Did you look at the attached file that describes the exporting?  You have sent me a link to how to define User Names.

Please take a look at the original request for a macro.


Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
did you check the attached file in my previous comment ID: 40938506? the notes for User Names is to highlight to you how to create it in case you not sure how to do it.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
click on the blue icon and it will trigger the action.
Bright01Author Commented:
It only downloads a zip file with nothing recognizable in it.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try to rename the file D--Data-Data-Temp-Industry-DB-Export_b.x to D--Data-Data-Temp-Industry-DB-Export_b.xlsm

EE got problem to upload a long file name, the file name seems truncated.
Bright01Author Commented:

Took your advise and this works!  Almost.  Is there a way that when you draw down the drop box that it actually allows you to select the location on your Harddrive, not because you have a pre configured List but because it simply views into your Harddrive?  This is how most normal export functions work and I just wanted to see if that was possible in Excel.  One other question.... if I don't want all of the additional info. that is tagged to the exported file, do I simply remove;     & Format(Now(), "YYYYMMDD HHMMSS")?


Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>Is there a way that when you draw down the drop box that it actually allows you to select the location on your Harddrive, not because you have a pre configured List but because it simply views into your Harddrive?

try this:

' Browse for a Folder using SHBrowseForFolder API function with a callback
' function BrowseCallbackProc.
' This Extends the functionality that was given in the
' MSDN Knowledge Base article Q179497 "HOWTO: Select a Directory
' Without the Common Dialog Control".
' After reading the MSDN knowledge base article Q179378 "HOWTO: Browse for
' Folders from the Current Directory", I was able to figure out how to add
' a callback function that sets the starting directory and displays the
' currently selected path in the "Browse For Folder" dialog.
' I used VB 6.0 (SP3) to compile this code.  Should work in VB 5.0.
' However, because it uses the AddressOf operator this code will not
' work with versions below 5.0.
' This code works in Window 95a so I assume it will work with later versions.
' Stephen Fonnesbeck
' steev@xmission.com
' http://www.xmission.com/~steev
' Feb 20, 2000
' Usage:
'    Dim folder As String
'    folder = BrowseForFolder(Me, "Select A Directory", "C:\startdir\anywhere")
'    If Len(folder) = 0 Then Exit Sub  'User Selected Cancel

Option Explicit

Public Const BIF_RETURNONLYFSDIRS = &H1      'Only file system directories
Public Const BIF_DONTGOBELOWDOMAIN = &H2     'No network folders below domain level
Public Const BIF_STATUSTEXT = &H4            'Includes status area in the dialog (for callback)
Public Const BIF_RETURNFSANCESTORS = &H8     'Only returns file system ancestors
Public Const BIF_EDITBOX = &H10              'Allows user to rename selection
Public Const BIF_VALIDATE = &H20             'Insist on valid edit box result (or CANCEL)
Public Const BIF_USENEWUI = &H40             'Version 5.0. Use the new user-interface.
                                             'Setting this flag provides the user with
                                             'a larger dialog box that can be resized.
                                             'It has several new capabilities including:
                                             'dialog box, reordering, context menus, new
                                             'folders, drag and drop capability within
                                             'the delete, and other context menu commands.
                                             'To use you must call OleInitialize or
                                             'CoInitialize before calling SHBrowseForFolder.
Public Const BIF_BROWSEFORCOMPUTER = &H1000  'Only returns computers.
Public Const BIF_BROWSEFORPRINTER = &H2000   'Only returns printers.
Public Const BIF_BROWSEINCLUDEFILES = &H4000 'Browse for everything

Private Const MAX_PATH = 260

Private Const WM_USER = &H400
Private Const BFFM_INITIALIZED = 1
Private Const BFFM_SELCHANGED = 2
Private Const BFFM_SETSTATUSTEXT = (WM_USER + 100)
Private Const BFFM_SETSELECTION = (WM_USER + 102)

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32" (lpbi As BrowseInfo) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, ByVal lpBuffer As String) As Long
Private Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" (ByVal lpString1 As String, ByVal lpString2 As String) As Long

Private Type BrowseInfo
  hWndOwner      As Long
  pIDLRoot       As Long
  pszDisplayName As Long
  lpszTitle      As Long
  ulFlags        As Long
  lpfnCallback   As Long
  lParam         As Long
  iImage         As Long
End Type

Private m_CurrentDirectory As String   'The current directory

Public Function BrowseForFolder(Optional Title As String = "Select a directory", Optional StartDir As String = "", Optional ShowNewFolderButton As Boolean = False, Optional ShowStatusText As Boolean = False, Optional ShowTextBox As Boolean = False, Optional BrowseIncludeFiles As Boolean = False) As String
    'Opens a Treeview control that displays the directories in a computer
    Dim lpIDList As Long
    Dim szTitle As String
    Dim sBuffer As String
    Dim tBrowseInfo As BrowseInfo
    Dim mflags As Long
    m_CurrentDirectory = StartDir & vbNullChar
    'Display New Folder Button
    If ShowNewFolderButton Then mflags = mflags + 64
    'Display Status Text
    If ShowStatusText Then mflags = mflags + BIF_STATUSTEXT '+ BIF_VALIDATE
    'Display Text Box
    If ShowTextBox Then mflags = mflags + 16
    'Display Files
    If BrowseIncludeFiles Then mflags = mflags + BIF_BROWSEINCLUDEFILES
    szTitle = Title
    With tBrowseInfo
        .hWndOwner = 0
        .lpszTitle = lstrcat(szTitle, "")
        .lpfnCallback = GetAddressofFunction(AddressOf BrowseCallbackProc)  'get address of function.
    End With

    lpIDList = SHBrowseForFolder(tBrowseInfo)
    If (lpIDList) Then
        sBuffer = Space(MAX_PATH)
        SHGetPathFromIDList lpIDList, sBuffer
        sBuffer = Left(sBuffer, InStr(sBuffer, vbNullChar) - 1)
        BrowseForFolder = sBuffer
        BrowseForFolder = ""
    End If
End Function
Private Function BrowseCallbackProc(ByVal hWnd As Long, ByVal uMsg As Long, ByVal lp As Long, ByVal pData As Long) As Long
  Dim lpIDList As Long
  Dim ret As Long
  Dim sBuffer As String
  On Error Resume Next  'Sugested by MS to prevent an error from
                        'propagating back into the calling process.
  Select Case uMsg
      Call SendMessage(hWnd, BFFM_SETSELECTION, 1, m_CurrentDirectory)
      sBuffer = Space(MAX_PATH)
      ret = SHGetPathFromIDList(lp, sBuffer)
      If ret = 1 Then
        Call SendMessage(hWnd, BFFM_SETSTATUSTEXT, 0, sBuffer)
      End If
  End Select
  BrowseCallbackProc = 0
End Function

' This function allows you to assign a function pointer to a vaiable.
Private Function GetAddressofFunction(add As Long) As Long
  GetAddressofFunction = add
End Function

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"
    ActiveWorkbook.SaveAs Path & fileName, xlExcel8
    MsgBox Path & fileName & " saved successfully.", vbInformation, "Completed"
    'Dim wb As Workbook
    'For Each wb In Workbooks
    '    '...
End Sub

Open in new window

>>do I simply remove;     & Format(Now(), "YYYYMMDD HHMMSS")?
yes, of course. That's for testing purposes cheers

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:

I've been working to install the code you have recommended from Stephen F. but don't know where to put it (Module or in the WS itself)?  And how do I identify which cell represents the drop down box?

Thank you!  

Bright01Author Commented:

Great job!!!!  Thanks.....got it to work.  Will be issuing another question shortly.  

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>but don't know where to put it (Module or in the WS itself)?
yup, you can put it into a Module, or you can simply modify the sample I have uploaded in comment: ID: 40938764

Again, the file name was truncated, need to rename from D--Data-Data-Temp-Industry-DB-Export_c.x to D--Data-Data-Temp-Industry-DB-Export_c.xlsm

>> And how do I identify which cell represents the drop down box?
In this new sample, there's no need to have a dropdown as it use Windows API to trigger a browse for folder dialog.

if you mean for the older sample, the dropdown list is referring to cell : C8
Bright01Author Commented:
New Post..... now for the Import capability!  ;-)

[ fanpages ]IT Services ConsultantCommented:
A link to the question, for any interested party:

[ http://www.experts-exchange.com/questions/28707166/Import-WS-and-Replace-Tab.html ]


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 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 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)?
Bright01Author Commented:
Well, I think the user will have a file folder to save the WS and once established, will access the same file to import the WS.  The reason the name has to be the same is that in my WB, there are macros that refer to the name of the WS.... so it has to be consistent.

Make sense?

[ fanpages ]IT Services ConsultantCommented:
Oh yes, I understand the logic, but users do have a habit of clicking on different folders, or accidentally selecting different drives/folders, & I was just alerting you to the potential issue that unless you implement a method of determining that the correct (most recent/intended) file is being imported, you may run into problems at a later stage of processing.

Simply displaying the selected file's last modified date/time, & asking the user to confirm this is the correct file, may be sufficient.

Adding functionality to not import files saved over 30 days ago, or something similar, is also an option.

I was just offering a suggestion to make your life easier in the long-term :)
Bright01Author Commented:
Great suggestion!  So ideally, if the file was saved with Date/time info., and it was retrieved (I.e. imported) back into the WB, could it strip out the D/T info and replace the name exactly as it was in the WB (i.e. Same name, newer or correct copy)?

Bright01Author Commented:
Fanpages, are you still planning a response here or should I author a new question?
[ fanpages ]IT Services ConsultantCommented:
Sorry B, I have just caught-up with my backlog of e-mail notifications & seen this question (again).

I think we have moved on since the last comment or so but, if not, please post again.

Bright01Author Commented:
Great job!  Thank you so much for the help.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.