How do I convert an EXCEL report into one record for import to ACC

M

Mike M

How can I set create a VBA program that can arrange a multi-row EXCEL 2007
report by consolidating the lines into one record to import it into ACCESS
2007?

Example: How can I convert all of the information below into one record?

Location Title
DATE
Prev Current Used Cost Sold Purchased
Liquor 14407.60 14767.66 819.71 $535.32 731.00 $731.30
Wine 21399.49 21989.17 931.93 $404.89 1211.06$441.72
Beer 1417 1268 341 $297.78 333 $163.60
 
P

Patricia Shanahan

Mike said:
How can I set create a VBA program that can arrange a multi-row EXCEL 2007
report by consolidating the lines into one record to import it into ACCESS
2007?

Example: How can I convert all of the information below into one record?

Location Title
DATE
Prev Current Used Cost Sold Purchased
Liquor 14407.60 14767.66 819.71 $535.32 731.00 $731.30
Wine 21399.49 21989.17 931.93 $404.89 1211.06$441.72
Beer 1417 1268 341 $297.78 333 $163.60

I hesitated to reply, because I'm a VBA novice and don't have exactly
what you need. Since I have not seen any other replies, I'm going to
post what I do have. You may be able to either adapt it, or use some of
the ideas.

My problem is to output the columns of a table as LaTeX source for the
rows of a tabular. That means "&" as column separator, "\\" as row
separator. My code contains a built-in transposition that you probably
do not want. If so, you need to exchange the two loops so that the row
loop is outer. formatNumber is a function that converts to String doing
some special formatting of numbers.


' Convert the range with the specified corners to a LaTeX table
' transposing it in the process so that each column of the
' range becomes a row in the LaTeX.
Function transposeRangeToLaTeX(cornerBase As Range, oppositeCorner As
Range) As String
Dim result As String
result = vbCrLf
Dim col As Integer
Dim row As Integer
For col = 0 To oppositeCorner.column - cornerBase.column
For row = 0 To oppositeCorner.row - cornerBase.row
If (row > 0) Then
result = result & "&"
End If
result = result & formatNumber((cornerBase.Offset(row, col).value))
Next row
result = result & "\\" & vbCrLf
Next col
transposeRangeToLaTeX = result
End Function
 
C

Clif McIrvin

Mike - My question relates to database design / structure: What you are
asking will result in data that is far from normalized, hence will
ultimately prove to be unduly difficult to work with. Perhaps you
already understand database normalization and have a plan for dealing
with the non-normalized data -- Patricia has given you an example that
you can modify to accomplish what you are after.

If you do intend to normalize the data after you get it into Access, I
might suggest a slightly different approach:

Using Patricia's code as a starting point, transform your Excel report
into a structure that looks like this:

Location Title DATE Item Prev Current Used Cost
Sold Purchased
Location Title DATE Liquor 14407.60 14767.66 819.71 $535.32
731.00 $731.30
Location Title DATE Wine 21399.49 21989.17 931.93 $404.89
1211.06$441.72
Location Title DATE Beer 1417 1268 341 $297.78
333 $163.60

Then link to the resulting worksheet and massage the data with queries.
Also, you might find it more trouble-free to export the resulting
worksheet to a CSV file and link to that instead.
 
C

Clif McIrvin

Patricia - I noticed you described yourself as a VBA novice.

Your code will work wonderfully as long as cornerBase is the upper left
corner of the range; but as Excel will allow you to select a range from
any pair of opposite corners changing these two lines as follows will
keep your code from breaking if cornerBase is not the upper left corner:
For col = 0 To abs(oppositeCorner.column - cornerBase.column)
For row = 0 To abs(oppositeCorner.row - cornerBase.row)

Using the Abs() function will always yield a positive number.

(Perhaps you already check for that before you call your function <g>.)
 
P

Patricia Shanahan

As it happens, this function was written to be called from one of my own
macros, which always passes the top left and bottom right corners of a
table it has just constructed. However, I prefer to make my code robust
and reusable, so I'll try to remember to deal with other pairs of
opposite corners in future.

I'm not sure how your suggested change would work in practice.
Suppose, for example, cornerBase were the bottom left corner, instead of
top left. I would need row to go negative to get to rows above
cornerBase. Perhaps the code would need more reworking to deal with the
general case?

Thanks.

Patricia
 
C

Clif McIrvin

Patricia Shanahan said:
As it happens, this function was written to be called from one of my
own
macros, which always passes the top left and bottom right corners of a
table it has just constructed. However, I prefer to make my code
robust
and reusable, so I'll try to remember to deal with other pairs of
opposite corners in future.

I'm not sure how your suggested change would work in practice.
Suppose, for example, cornerBase were the bottom left corner, instead
of
top left. I would need row to go negative to get to rows above
cornerBase. Perhaps the code would need more reworking to deal with
the
general case?

You're right, of course. Your nested loops do need to operate from the
top left corner.

There are a variety of ways to deal with this issue, and while I do have
programming background I've only recently had occasion to work with VBA
and Excel -- so I'm all the time discovering some new way to use native
properties and methods; or even discovering properties or methods I
didn't even realize were there.

Some untested air code for taking a pair of opposite corners and
determining upper left and lower right:

Option Explicit

Function GetUpperLeft(ByRef Corner1 As Range, _
ByRef Corner2 As Range, _
ByRef UpperLeft As Range, _
ByRef LowerRight As Range) As Long

' Returns the count of cells in the region defined by
' the pair of opposite corners Corner1 and Corner2.

' Exits with UpperLeft and LowerRight pointing to those
' respective cells.

' Error checking could be added to test for a variety of
' conditions, such as Corner1 and Corner2 not belonging
' to the same worksheet, or either or both objects not
' being a single cell. As written, no error checking is
' performed, and the upper left cell of each is taken as
' the reference, if either object is a range of more than
' one cell.

' If desired, negative return values could be defined to
' report specific error conditions; ie:
' -1 = Corner1 contains multiple cells
' -2 = Corner2 contains multiple cells
' -3 = Both contain multiple cells
' etc.

Dim cellCount As Long ' Number of cells in specified region
Dim Temp As Range ' Temporary Range object

Set Temp = Range(Corner1.Cells(1), Corner2.Cells(1))
cellCount = Temp.Count
Set UpperLeft = Temp.Cells(1)
Set LowerRight = Temp.Cells(cellCount)

GetUpperLeft = cellCount
Set Temp = Nothing

End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top