[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

How convert Excel file from a single column to a single row via VBA

Experts just solved an issue for me where I needed to convert data to be viewed via an Excel file from a single row to a single column.

Now I have to do just the reverse.  I have a form that I use to select the Excel file that looks like...

Current
But I now need to make it look like:

Needed
How can I do this? The reason is because I'm going to write the values to a temp table via:

DoCmd.TransferSpreadsheet acImport, 10, "tblTempNotesImport", Me.txtFileName, True

Open in new window


--Steve
0
SteveL13
Asked:
SteveL13
  • 3
  • 3
2 Solutions
 
Gustav BrockCIOCommented:
0
 
hnasrCommented:
Gustav's comment does transform the record, but does not guarantee the order of columns.
To do that, a bit of VBA code is required.
0
 
Gustav BrockCIOCommented:
No, it sorts the columns from the field you pivot on.
If you need another order or to exclude some fields, you can apply In:

TRANSFORM First([F2]) AS FirstValue
SELECT "Values" AS Field
FROM tblYourTable
GROUP BY "Values"
PIVOT [F1] In (<FirstValue>, <SecondValue>, ... , <LastValue);

/gustav
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
hnasrCommented:
"it sorts the columns from the field you pivot on."
This is what I meant.

The VBA code is to automate the arrangement of the columns as they are originally arranged.
0
 
hnasrCommented:
Try this sample database.
Compare results by running crosstab query and that using the code running from the form.
transformPlusCode.accdb
0
 
Gustav BrockCIOCommented:
But you wrote "guarantee" as if the sequence was or could be random. It is not.

/gustav
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now