Append Data with Custom Hashtag, Automate with VBA & Button

T

ThePianoman

New Member
Joined
Mar 15, 2011
Messages
15
I'm looking for a bit of help for a pretty straightforward small project. I'm fairly familiar with all the basic functions of excel, VLOOKUP, pivot tables, etc., but clueless when it comes to VBA. I realize it can probably be done manually pretty easily, or using helper columns or similar, but I will need to perform the function several times and make it easy for other research study sites to use. So automated with VBA is the way I want to go. I'm willing to donate a small fee to whoever can work with me on this- you guys are the experts.

Here's a brief rundown:


  • I will periodically export a dataset from a research study database, in excel format. It will look something like this (with exactly this many columns, but potentially many more rows of data):

[TABLE="width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88"]demo_mrn[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl65, width: 127"]ffi_shrinking_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl65, width: 146"]ffi_gripstrength_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl65, width: 147"]ffi_slowwalking_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="class: xl65, width: 138"]ffi_lowactivity_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl65, width: 74"]ffi_fr[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"]cfs_cfsscore[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl65, width: 100"]rai_score_total[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11111111[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]22222222[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]33333333[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]44444444[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]55555555[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]66666666[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]77777777[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]88888888[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]77[/TD]
[/TR]
</tbody>[/TABLE]


  • The data (B2:H9) needs to have "#F4_[column header in row 1]_" appended to it so that it will look exactly like this:

[TABLE="width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88"]demo_mrn[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl65, width: 127"]ffi_shrinking_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl65, width: 146"]ffi_gripstrength_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl65, width: 147"]ffi_slowwalking_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="class: xl65, width: 138"]ffi_lowactivity_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl65, width: 74"]ffi_fr[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"]cfs_cfsscore[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl65, width: 100"]rai_score_total[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11111111[/TD]
[TD]#F4_ffi_shrinking_score_0[/TD]
[TD]#F4_ffi_gripstrength_score_1[/TD]
[TD]#F4_ffi_slowwalking_score_0[/TD]
[TD]#F4_ffi_lowactivity_score_0[/TD]
[TD]#F4_ffi_fr_1[/TD]
[TD]#F4_cfs_cfsscore_1[/TD]
[TD]#F4_rai_score_total_89[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]22222222[/TD]
[TD]#F4_ffi_shrinking_score_3[/TD]
[TD]#F4_ffi_gripstrength_score_2[/TD]
[TD]#F4_ffi_slowwalking_score_2[/TD]
[TD]#F4_ffi_lowactivity_score_1[/TD]
[TD]#F4_ffi_fr_3[/TD]
[TD]#F4_cfs_cfsscore_4[/TD]
[TD]#F4_rai_score_total_55[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]33333333[/TD]
[TD]#F4_ffi_shrinking_score_1[/TD]
[TD]#F4_ffi_gripstrength_score_2[/TD]
[TD]#F4_ffi_slowwalking_score_1[/TD]
[TD]#F4_ffi_lowactivity_score_1[/TD]
[TD]#F4_ffi_fr_0[/TD]
[TD]#F4_cfs_cfsscore_3[/TD]
[TD]#F4_rai_score_total_67[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]44444444[/TD]
[TD]#F4_ffi_shrinking_score_1[/TD]
[TD]#F4_ffi_gripstrength_score_1[/TD]
[TD]#F4_ffi_slowwalking_score_1[/TD]
[TD]#F4_ffi_lowactivity_score_1[/TD]
[TD]#F4_ffi_fr_4[/TD]
[TD]#F4_cfs_cfsscore_1[/TD]
[TD]#F4_rai_score_total_42[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]55555555[/TD]
[TD]#F4_ffi_shrinking_score_0[/TD]
[TD]#F4_ffi_gripstrength_score_0[/TD]
[TD]#F4_ffi_slowwalking_score_1[/TD]
[TD]#F4_ffi_lowactivity_score_2[/TD]
[TD]#F4_ffi_fr_2[/TD]
[TD]#F4_cfs_cfsscore_2[/TD]
[TD]#F4_rai_score_total_25[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]66666666[/TD]
[TD]#F4_ffi_shrinking_score_3[/TD]
[TD]#F4_ffi_gripstrength_score_1[/TD]
[TD]#F4_ffi_slowwalking_score_2[/TD]
[TD]#F4_ffi_lowactivity_score_1[/TD]
[TD]#F4_ffi_fr_3[/TD]
[TD]#F4_cfs_cfsscore_4[/TD]
[TD]#F4_rai_score_total_67[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]77777777[/TD]
[TD]#F4_ffi_shrinking_score_4[/TD]
[TD]#F4_ffi_gripstrength_score_1[/TD]
[TD]#F4_ffi_slowwalking_score_3[/TD]
[TD]#F4_ffi_lowactivity_score_3[/TD]
[TD]#F4_ffi_fr_2[/TD]
[TD]#F4_cfs_cfsscore_1[/TD]
[TD]#F4_rai_score_total_84[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]88888888[/TD]
[TD]#F4_ffi_shrinking_score_1[/TD]
[TD]#F4_ffi_gripstrength_score_4[/TD]
[TD]#F4_ffi_slowwalking_score_3[/TD]
[TD]#F4_ffi_lowactivity_score_2[/TD]
[TD]#F4_ffi_fr_2[/TD]
[TD]#F4_cfs_cfsscore_1[/TD]
[TD]#F4_rai_score_total_77[/TD]
[/TR]
</tbody>[/TABLE]


  • The dataset could include up to 1500 rows. Preferably I would like the VBA function to be initiated with a button. So it would work like this:

  1. Export the data to excel.
  2. Copy/Paste data into the macro worksheet
  3. Click the button to convert/append the data
  4. Click another button to create a new workbook (regular excel file), and copy the converted data into it.

Thanks in advance for your help.
 
Sort by date Sort by votes
I'm assuming that the number of columns is fixed. If so, your first macro is:
Code: 
Sub AppendMacro()
With ActiveSheet
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Application.ScreenUpdating = False
For DataCol = 2 to 8
  For DataRow = 2 to LastRow
    .Cells(DataRow, DataCol).Value = "#F4_" & .Cells(1, DataCol).Value & "_" & .Cells(DataRow, DataCol).Value
  Next
Next
Application.ScreenUpdating = True
End With
End Sub
The macro file will be a .xlsm file. The following macro will save it as a .xlsx file with the same name:
Code: 
Sub SaveXlsx()
fName = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5) & ".xlsx"
ActiveWorkbook.SaveAs(fName)
End Sub
 
Last edited:
Upvote 0

Thanks Trevor, this is great. I do get the following error:

"Run-time error '1004': This extension can not be used with the selected file type. Change the file extension in the File name text box or select a different file type by changing the Save as type"

I would actually prefer to have the worksheet copied to a new workbook, and the "save as" prompt to appear so I can choose location and name of the new file. Is that doable?

Thanks.
 
Upvote 0
Try this for the second macro:
Code: 
Sub SaveXlsx()
Dim ThisWb As Workbook
Dim NewWb As Workbook
Set ThisWb = ActiveWorkbook
Set NewWb = Workbooks.New
ThisWb.ActiveSheet.Range("A:H").Copy
NewWb.ActiveSheet.Range("A:H").Select
Selection.Paste
fName = Application.GetSaveAsFilename(,"Excel Workbook (*.xlsx), *.xlsx")
If fName = "" then
  'User cancelled, do not save file
Else
  NewWb.SaveAs(fName)
End If
End Sub
 
Last edited:
Upvote 0
Glad that it worked.
Its a free forum! I've learned a lot from it over the years, so I'm always happy to answer any threads that I can.
 
Upvote 0
Well thanks! I feel like I'm robbing you of your knowledge. I appreciate the help, I wish I knew a bit more VBA to to do this on my own.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA to automate calculation
Replies
1
Views
222
HighAndWilder
H
F
  • Solved
VBA - Append CSV data to current file
2
Replies
12
Views
1K
Domenic
D
G
  • Question Question
Stop sort after data is sorted with button
Replies
6
Views
199
Peter_SSs
Peter_SSs
P
  • Question Question
Hoping to get help with a Macro!
Replies
0
Views
318
PEM000
P
M
  • Question Question
Macro/Automating Edits and Calculations for Large Report (40K rows)
Replies
6
Views
244
kevin9999
K

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top