Windows 8 - Moving Users Folder from SSD to HDD - "local volumes are required to complete the operation", cannot complete system restore

Question

First off, I am a beginner in VBA and Power Query, so I am somewhat stumbling through this process.

I have a macro that includes a power query (that takes an existing data set and creates a table, deleting the rest of the dataset). I ran it one time successfully, and then it started throwing an a run-time error "A query with the name 'Table1' already exists'. Here is the vba code, excluding a section before the power query bit that creates custom columns needed for the query to run -

Range("A1:EJ500").Select

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$EJ$500"), , xlYes).Name = _
    "Table1"
Range("Table1[#All]").Select
ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Interviewer Last Name"", type text}, {""CMR used for family"", type any}, {""LHD Investigation Completed"", type datetime}, {""QT Day Zero based on Last HH Onset"", type datetime}, {""Test 1"", type text}, {""Test 2"", type text}, {""" & _
    "Test 3"", type text}, {""Test 4"", type text}, {""Test 5"", type text}, {""Test 6"", type text}, {""Test 7"", type text}, {""Test 8"", type text}, {""Test 9"", type text}, {""Test 10"", type text}, {""Test 11"", type text}, {""Test 12"", type text}, {""Test 13"", type text}, {""Test 14"", type text}, {""Test 15"", type text}, {""Test 16"", type text}, {""Household C" & _
    "ontact CMR Field 1"", Int64.Type}, {""Household Contact First Name 1"", type text}, {""Household Contact Last Name 1"", type text}, {""Household Contact DOB 1"", type datetime}, {""Household Contact Status "", type text}, {""School District 1"", type text}, {""School Name 1"", type text}, {""Staff or Student 1"", type text}, {""Date Last Attended 1"", type datetime}" & _
    ", {""Household Contact Date of Last Exposure 1"", type datetime}, {""Household Contact Ongoing Exposure Question 1"", type text}, {""HH onset date"", type datetime}, {""Household Contact CMR Field 2"", Int64.Type}, {""Household Contact First Name 2"", type text}, {""Household Contact Last Name 2"", type text}, {""Household Contact DOB 2"", type datetime}, {""Househo" & _
    "ld Contact Status 2"", type text}, {""School District 2"", type text}, {""School Name 2"", type text}, {""Staff or Student 2"", type text}, {""Date Last Attended 2"", type datetime}, {""Household Contact Date of Last Exposure 2"", type datetime}, {""Household Contact Ongoing Exposure Question 2"", type text}, {""HH onset date3"", type datetime}, {""Household Contact" & _
    " CMR Field 3"", Int64.Type}, {""Household Contact First Name 3"", type text}, {""Household Contact Last Name 3"", type text}, {""Household Contact DOB 3"", type datetime}, {""Household Contact Status 4"", type text}, {""School District 3"", type text}, {""School Name 3"", type text}, {""Staff or Student 3"", type text}, {""Date Last Attended 3"", type datetime}, {""" & _
    "Household Contact Date of Last Exposure 3"", type datetime}, {""Household Contact Ongoing Exposure Question 3"", type text}, {""HH onset date5"", type datetime}, {""Household Contact CMR Field 4"", Int64.Type}, {""Household Contact First Name 4"", type text}, {""Household Contact Last Name 4"", type text}, {""Household Contact DOB 4"", type datetime}, {""Household C" & _
    "ontact Status 6"", type text}, {""School District "", type text}, {""School Name "", type text}, {""Staff or Student "", type text}, {""Date Last Attended "", type datetime}, {""Household Contact Date of Last Exposure 4"", type datetime}, {""Household Contact Ongoing Exposure Question 4"", type text}, {""HH onset date7"", type datetime}, {""Household Contact CMR Fie" & _
    "ld 5"", Int64.Type}, {""Household Contact First Name 5"", type text}, {""Household Contact Last Name 5"", type text}, {""Household Contact DOB 5"", type datetime}, {""Household Contact Status 8"", type text}, {""School District 9"", type text}, {""School Name 10"", type text}, {""Staff or Student 11"", type text}, {""Date Last Attended 12"", type datetime}, {""House" & _
    "hold Contact Date of Last Exposure 5"", type datetime}, {""Household Contact Ongoing Exposure Question 5"", type text}, {""HH onset date13"", type datetime}, {""Household Contact CMR Field 6"", Int64.Type}, {""Household Contact First Name 6"", type text}, {""Household Contact Last Name 6"", type text}, {""Household Contact DOB 6"", type datetime}, {""Household Conta" & _
    "ct Status 14"", type text}, {""School District 15"", type text}, {""School Name 16"", type text}, {""Staff or Student 17"", type text}, {""Date Last Attended 18"", type datetime}, {""Household Contact Date of Last Exposure 6"", type datetime}, {""Household Contact Ongoing Exposure Question 6"", type text}, {""HH onset date19"", type datetime}, {""Household Contact C" & _
    "MR Field 7"", Int64.Type}, {""Household Contact First Name 7"", type text}, {""Household Contact Last Name 7"", type text}, {""Household Contact DOB 7"", type datetime}, {""Household Contact Status 20"", type text}, {""School District 21"", type text}, {""School Name 22"", type text}, {""Staff or Student 23"", type text}, {""Date Last Attended 24"", type datetime}, " & _
    "{""Household Contact Date of Last Exposure 7"", type datetime}, {""Household Contact Ongoing Exposure Question 7"", type text}, {""HH onset date25"", type datetime}, {""Household Contact CMR Field 8"", Int64.Type}, {""Household Contact First Name 8"", type text}, {""Household Contact Last Name 8"", type text}, {""Household Contact DOB 8"", type datetime}, {""Househo" & _
    "ld Contact Status 26"", type text}, {""School District 27"", type text}, {""School Name 28"", type text}, {""Staff or Student 29"", type text}, {""Date Last Attended 30"", type datetime}, {""Household Contact Date of Last Exposure 8"", type datetime}, {""Household Contact Ongoing Exposure Question 8"", type text}, {""HH onset date31"", type datetime}, {""Household C" & _
    "ontact CMR Field 9"", Int64.Type}, {""Household Contact First Name 9"", type text}, {""Household Contact Last Name 9"", type text}, {""Household Contact DOB 9"", type datetime}, {""Household Contact Status 32"", type text}, {""School District 33"", type text}, {""School Name 34"", type text}, {""Staff or Student 35"", type text}, {""Date Last Attended 36"", type dat" & _
    "etime}, {""Household Contact Date of Last Exposure 9"", type any}, {""Household Contact Ongoing Exposure Question 9"", type any}, {""HH onset date37"", type datetime}, {""Household Contact CMR Field 10"", Int64.Type}, {""Household Contact First Name 10"", type text}, {""Household Contact Last Name 10"", type text}, {""Household Contact DOB 10"", type datetime}, {""H" & _
    "ousehold Contact Status 38"", type text}, {""School District 39"", type text}, {""School Name 40"", type text}, {""Staff or Student 41"", type text}, {""Date Last Attended 42"", type datetime}, {""Household Contact Date of Last Exposure 10"", type any}, {""Household Contact Ongoing Exposure Question 10"", type any}, {""HH onset date43"", type datetime}})," & Chr(13) & "" & Chr(10) & "    #""Re" & _
    "placed Value"" = Table.ReplaceValue(#""Changed Type"",null,"""",Replacer.ReplaceValue,{""Interviewer Last Name"", ""CMR used for family"", ""LHD Investigation Completed"", ""QT Day Zero based on Last HH Onset"", ""Test 1"", ""Test 2"", ""Test 3"", ""Test 4"", ""Test 5"", ""Test 6"", ""Test 7"", ""Test 8"", ""Test 9"", ""Test 10"", ""Test 11"", ""Test 12"", ""Test 13" & _
    """, ""Test 14"", ""Test 15"", ""Test 16"", ""Household Contact CMR Field 1"", ""Household Contact First Name 1"", ""Household Contact Last Name 1"", ""Household Contact DOB 1"", ""Household Contact Status "", ""School District 1"", ""School Name 1"", ""Staff or Student 1"", ""Date Last Attended 1"", ""Household Contact Date of Last Exposure 1"", ""Household Contact " & _
    "Ongoing Exposure Question 1"", ""HH onset date"", ""Household Contact CMR Field 2"", ""Household Contact First Name 2"", ""Household Contact Last Name 2"", ""Household Contact DOB 2"", ""Household Contact Status 2"", ""School District 2"", ""School Name 2"", ""Staff or Student 2"", ""Date Last Attended 2"", ""Household Contact Date of Last Exposure 2"", ""Household " & _
    "Contact Ongoing Exposure Question 2"", ""HH onset date3"", ""Household Contact CMR Field 3"", ""Household Contact First Name 3"", ""Household Contact Last Name 3"", ""Household Contact DOB 3"", ""Household Contact Status 4"", ""School District 3"", ""School Name 3"", ""Staff or Student 3"", ""Date Last Attended 3"", ""Household Contact Date of Last Exposure 3"", ""H" & _
    "ousehold Contact Ongoing Exposure Question 3"", ""HH onset date5"", ""Household Contact CMR Field 4"", ""Household Contact First Name 4"", ""Household Contact Last Name 4"", ""Household Contact DOB 4"", ""Household Contact Status 6"", ""School District "", ""School Name "", ""Staff or Student "", ""Date Last Attended "", ""Household Contact Date of Last Exposure 4""" & _
    ", ""Household Contact Ongoing Exposure Question 4"", ""HH onset date7"", ""Household Contact CMR Field 5"", ""Household Contact First Name 5"", ""Household Contact Last Name 5"", ""Household Contact DOB 5"", ""Household Contact Status 8"", ""School District 9"", ""School Name 10"", ""Staff or Student 11"", ""Date Last Attended 12"", ""Household Contact Date of Last " & _
    ""


ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table1"";Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [Table1]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "Table1__2"
    .Refresh BackgroundQuery:=False
End With

In order to fix the query name issue, I inserted this at the beginning, right after "Range("A1:EJ500").Select" -

On Error Resume Next
ActiveWorkbook.Queries("Table1").Delete
On Error GoTo 0
wb.Activate
wb.Sheets("Sheet1").Range("Sheet1[#All]").Select

Then running the macro started giving me a "run-time error 1004, the name 'Source' wasn't recognized. Make sure it's spelled correctly." So I modified Range("Table1[#All]").Select to "wb.Sheets("Sheet1").Range("Table1[#All]").Select" based off of reading other posts, and now I get "run-time error 1004. Select method of Range class failed." I tried modifying that and I just continue to get those same errors, so I'm starting to get confused about where the problem is.

Any assistance?

Solution

No, use the User State Migration Tool from Microsoft! You're making this more complicated than it needs to be!

In fact, the EASIEST thing is the windows easy transfer tool, its built on the same technology, but it's designed for end users.

http://windows.microsoft.com/en-us/windows7/products/features/windows-easy-transfer

Comments

[Vixxd]

Hey MDT Guy, thanks so much :D I'd already downloaded the Windows Assessment and Deployment Kit (Windows ADK) for Windows 8.1 to grab the User State Migration tool from before I saw your edit, but I'll try that and also have a look at the Windows Easy Transfer tool. I wish results for 'Move users from SSD to HDD' had more info about that. Thanks.

[MDT Guy]

USMT's really for enterprises and has no GUI, it's all commandline driven, so you may have better luck with it's little cousin...

[Vixxd]

The Easy Transfer Tool appears to be having difficulties running on 8.1 - and from what I'm seeing it just appears to be for migrating from previous versions of Windows to newer ones. Would the User State Migration tool simply have an option to move C:\Users to B:\Users and allow me to still log in as normal?

[MDT Guy]

I know for a fact the new USMT that came with ADK 8.1 works with 8.1.

[Vixxd]

Yep - it's the Easy Transfer Tool that doesn't work with 8.1. I'm just looking through the USMT commands now to work out how to migrate C:\Users to B:\Users using the Command Line start up option :) Just to confirm: I'm not upgrading from an old version of Windows to a newer version - I just want to move my users folder from my SSD to the HDD in an already installed version of Windows 8.1. Just checking this tool also handles that? I've never used it before - sorry!

[MDT Guy]

Why do you have a drive assigned as B? That's not cool.

[Vixxd]

Because B hasn't been used for floppy disks in years so it doesn't really matter anyway as I'm the only person using this machine :p That aside, I'm not finding any clear instructions on using USMT to move a folder between drives on the same machine for the same installation of Windows 8.1 - am I missing something obvious?

[MDT Guy]

Please see: technet.microsoft.com/en-us/library/dd883247(v=ws.10).aspx

[Vixxd]

I'm finding this significantly more convoluted than the mklink approach :( I do not have a network share to create a migration store, and that article talks about logging in and out of different machines, I'm just working from the one machine with the one installation of the one OS. I don't want to "migrate" - I just literally want to force a link or junction from C:\Users to B:\Users. Or am I totally missing where the article covers that?