EE.xlsm

<div>
Wow. &nbsp;I couldn't even write it that fast if I had known what to do to start with. <br />
<br />
Will give this a shot and let you know.<br />
<br />
Thank you!<br />
<br />
--Ben
</div>


<div>
Professor,<br />
I think the &quot;GetDirectory&quot; procedure is missing from your code to pick the location to save the new workbooks. &nbsp; &nbsp;Code works well though once that is included.
</div>


<div>
Jerry,<br />
<br />
if you open the attachment which i posted along with the code, in its module &quot;BrowseSupliment&quot; it does contain the &quot;GetDirectory&quot; function.<br />
<br />
what Ben asked can be achieved only up to the 35th line of the code, i provided extra code, in case if Ben wanted to save the sheets into separate workbooks.
</div>


<div>
My apologies... &nbsp;I did not download the attachment. &nbsp;Only executed the code that was in the comment block above. &nbsp; Again, well done!
</div>


<div>
no problem Jerry.<br />
<br />
thanks.
</div>


<div>
Am nearly complete creating the spreadsheet this will run against. &nbsp;Looking at the code, I think I can eliminate the first half of it where it splits the single tab into multiple tabs prior to shipping them out to different spreadsheets. &nbsp;I just did that in the application I wrote instead. &nbsp;<br />
<br />
Should be able to test it later today.
</div>


<div>
BTW, this will end up with 4000-4500 tabs. &nbsp;That won't be a problem will it?
</div>


<div>
Hi Ben,<br />
<br />
Number of worksheet limit is dependent upon available memory . Do you have 32 bit or 64 bit office?
</div>


<div>
And each tab will be fairly small, about 8 columns and max 100 rows.
</div>


<div>
Oh. &nbsp;Wait. &nbsp;I won't be running it, someone else will. &nbsp;I don't know what their footprint looks like. &nbsp;Oops.
</div>


<div>
let them try, and see it might work.
</div>


<div>
Just found they are on Excel 2010, 32 bit version.
</div>


<div>
Trying the code above now that I have the spreadsheet created and am getting an unknown function error for GetDirectory(). &nbsp;I don't see it in Excel 2016. &nbsp; Should I use Activeworkbook.Path or something similar? &nbsp;The error was on line:<br />
<br />
&nbsp; &nbsp; Folder = GetDirectory(Folder)<br />
<br />
--Ben
</div>


<div>
Hi Ben,<br />
<br />
yiu are getting that error because probably you did not copy the whole code from the attached workbook, please open the attached workbook that I posted in earlier post and there is also the UDF in its module &quot;BrowseSupliment&quot;<br />
Once you copy and paste that code with the existing code then you will not see error
</div>


<div>
Oops. &nbsp;Missed the xlm attachment. &nbsp;My bad. &nbsp;Thanks!<br />
<br />
--Ben
</div>


<div>
Just ran the spreadsheet in debug mode. &nbsp;Very nice! &nbsp;The app I developed already does the first part--splitting them out into separate tabs, so I can just lift the latter half of it and let it rip.<br />
<br />
Thanks very much!<br />
<br />
--Ben
</div>


<div>
Excellent example of splitting a sheet in a spreadsheet into multiple spreadsheets in an automated fashion.
</div>


<div>
You are welcome Ben, I am glad I was able to help
</div>


<div>
This is probably a separate question, but can this be automated? &nbsp;The client wants to be able to call this in an automated .bat file. &nbsp;Something like:<br />
<br />
&lt;path to excel&gt; &nbsp;&lt;spreadsheet&gt; &lt;path to macro&gt; &lt;path to destination folder&gt; &lt;split column&gt; &lt;# of title rows&gt;<br />
<br />
I'm not even sure this is possible. &nbsp;?<br />
<br />
--Ben
</div>


<div>
yes it is a separate question and it not related to Excel or VBA. i suggest you open a question in the top of related window scripts .
</div>


<div>
Thanks! &nbsp;Will do.<br />
<br />
--Ben
</div>


<div>
Professor,<br />
<br />
I have 64 bit, Excel 2016 and receive a Compile error when I try to open your xslm attachment. &nbsp;How do I fix that?<br />
<br />
--Christine
</div>


<div>
Can you please share on which line of the code the highlighter of debugger stops? &nbsp;I
</div>


<div>
Hi Professor, I got busy on another project and am now back to this one. &nbsp;The section highlighted is this:<br />
Declare Function SHGetPathFromIDList Lib &quot;shell32.dll&quot; _<br />
&nbsp; Alias &quot;SHGetPathFromIDListA&quot; (ByVal pidl As Long, ByVal pszPath As String) _<br />
&nbsp; As Long<br />
<br />
Declare Function SHBrowseForFolder Lib &quot;shell32.dll&quot; _<br />
Alias &quot;SHBrowseForFolderA&quot; (lpBrowseInfo As BROWSEINFO) As Long<br />
Thank you for your help!!
</div>


<div>
to make my the code in my answer work with both 32bit and 64bit. &nbsp;the entirecode in the module called &quot;BrowseSupliment&quot; to be replaced with the below.<br />
<br />
i have also uploaded the complete file.<br />
<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-42062869-1"><span>#If VBA7 Then</span>
<span>    Private Type BROWSEINFO</span>
<span>        hOwner As LongPtr</span>
<span>        pidlRoot As LongPtr</span>
<span>        pszDisplayName As String</span>
<span>        lpszTitle As String</span>
<span>        ulFlags As Long</span>
<span>        lpfn As LongPtr</span>
<span>        lParam As LongPtr</span>
<span>        iImage As Long</span>
<span>    End Type</span>
<span>                        </span>
<span>    Private Declare PtrSafe Function SHBrowseForFolder Lib &quot;shell32.dll&quot; Alias &quot;SHBrowseForFolderA&quot; _</span>
<span>        (lpBrowseInfo As BROWSEINFO) As LongPtr</span>
<span>        </span>
<span>        Declare PtrSafe Function SHGetPathFromIDList Lib &quot;shell32.dll&quot; Alias &quot;SHGetPathFromIDListA&quot; (ByVal pidl As LongPtr, ByVal pszPath As String) As LongPtr</span>
<span>        </span>
<span>         Dim bInfo As BROWSEINFO</span>
<span>    Dim path As String</span>
<span>    Dim r As LongPtr, x As LongPtr, pos As Integer</span>
<span>#Else</span>
<span>    Private Type BROWSEINFO</span>
<span>        hOwner As Long</span>
<span>        pidlRoot As Long</span>
<span>        pszDisplayName As String</span>
<span>        lpszTitle As String</span>
<span>        ulFlags As Long</span>
<span>        lpfn As Long</span>
<span>        lParam As Long</span>
<span>        iImage As Long</span>
<span>    End Type</span>
<span>                        </span>
<span>    Private Declare Function SHBrowseForFolder Lib &quot;shell32.dll&quot; Alias &quot;SHBrowseForFolderA&quot; _</span>
<span>        (lpBrowseInfo As BROWSEINFO) As Long</span>
<span>        </span>
<span>        Declare Function SHGetPathFromIDList Lib &quot;shell32.dll&quot; _</span>
<span>  Alias &quot;SHGetPathFromIDListA&quot; (ByVal pidl As Long, ByVal pszPath As String) _</span>
<span>  As Long</span>
<span></span>
<span>        </span>
<span>            Dim bInfo As BROWSEINFO</span>
<span>    Dim path As String</span>
<span>    Dim r As Long, x As Long, pos As Integer</span>
<span>#End If</span>
<span>Private Const BIF_RETURNONLYFSDIRS = &amp;H1</span>
<span></span>
<span></span>
<span>Function GetDirectory(Optional Msg) As String</span>
<span>'    Dim bInfo As BROWSEINFO</span>
<span>'    Dim path As String</span>
<span>'    Dim r As Long, x As Long, pos As Integer</span>
<span> </span>
<span>'   Root folder = Desktop</span>
<span>    bInfo.pidlRoot = 0&amp;</span>
<span></span>
<span>'   Title in the dialog</span>
<span>    If IsMissing(Msg) Then</span>
<span>        bInfo.lpszTitle = &quot;Select a folder.&quot;</span>
<span>    Else</span>
<span>        bInfo.lpszTitle = Msg</span>
<span>    End If</span>
<span>    </span>
<span>'   Type of directory to return</span>
<span>    bInfo.ulFlags = &amp;H1</span>
<span></span>
<span>'   Display the dialog</span>
<span>    x = SHBrowseForFolder(bInfo)</span>
<span>    </span>
<span>'   Parse the result</span>
<span>    path = Space$(512)</span>
<span>    r = SHGetPathFromIDList(ByVal x, ByVal path)</span>
<span>    If r Then</span>
<span>        pos = InStr(path, Chr$(0))</span>
<span>        GetDirectory = Left(path, pos - 1)</span>
<span>    Else</span>
<span>        GetDirectory = &quot;&quot;</span>
<span>    End If</span>
<span>End Function</span>
</code></pre>
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152732/32bit-and-64bit-all-versions.xlsm" target="_blank" class="file-inline" title="32bit-and-64bit-all-versions.xlsm (40 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>32bit-and-64bit-all-versions.xlsm</a>
</div>


32bit-and-64bit-all-versions.xlsm

<div>
Thanks! &nbsp;I used the original code as a starting point for the project I had. &nbsp;Would love to post what I developed but I don't own the code and don't have access to that system any longer. &nbsp;<br />
<br />
--Ben
</div>


<div>
<div class="content wysiwyg-content">
Hi,<br />
<br />
I have a spreadsheet I'll be creating with provider id as the 1st column and other fields from columns 2-11. &nbsp;I need to take this spreadsheet and a template spreadsheet and create new spreadsheets from the original, with the template info at the top of each of them. &nbsp;The name of the new spreadsheets would be the provider #.<br />
<br />
Working in Excel 2016. &nbsp; Would love to be able to do this in batch mode if possible, as there will be a lot of spreadsheets created.<br />
<br />
Can anyone give an approach to take that I can start with?<br />
<br />
Thanks!<br />
<br />
--Ben
</div>
</div>


Hi,

I have a spreadsheet I'll be creating with provider id as the 1st column and other fields from columns 2-11.  I need to take this spreadsheet and a template spreadsheet and create new spreadsheets from the original, with the template info at the top of each of them.  The name of the new spreadsheets would be the provider #.

Working in Excel 2016.   Would love to be able to do this in batch mode if possible, as there will be a lot of spreadsheets created.

Can anyone give an approach to take that I can start with?

Thanks!

--Ben

using VBA to convert single tab spreadsheet to multiple spreadsheets

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.