VBA Help...I need a lot

[James]

So this has been one of the harder ones that I have had to tackle. I receive
data from a vendor and than I am in charge of formatting it correctly before
I upload it into Oracle. Take a look at my problem, below is an example of
what the data looks like:
**All Data and Indigenous data vary with how many cells there are, it just
all depends on the sample they rand for us.

Row A B C
3 US138858
4 HGS ID 07-XXXX
5 Well Name Roots E-1
6 Depth 1 (ft.) 6900
7 All Data Indigenous Data
8 0.75 0.77
9 0.76 0.79
10 0.77 0.8
11 0.79 0.8
12 0.8 0.82
13 0.8 0.82
14 0.82 0.83
15 0.82 0.83
16 0.83 0.85
17 0.83 0.86
18 0.85
19 0.86
20 0.89
21 0.95
22
23 Average %Ro 0.73 0.73
24 Standard Dev. 0.06
25 # of Points 40 36
26 MEAN_USED 0.73
27 STDDEV_USED 0.06
28 MIN_USED 0.65
29 MAX_USED 0.86
30 MIN_ALL 0.53
31 MAX_ALL 0.95
32 MEDIAN_USED 0.72
33 MODE_USED 0.72
34 RDGS_ALL 40
35 RDGS_USED 36


I have a template spreadsheet that when I run my macro it ask's me to pick
where my data comes from (that much of the macro I can do the next part is
just out of grasp). Below you will find how my template is set up.

Column Column Title What it refers to on the data sheet
A SAMPLE_ID B3
B PREP
C REQNUM
D MEAN_USED C26
E STDDEV_USED C27
F MIN_USED C28
G MAX_USED C29
H MIN_ALL C30
I MAX_ALL C31
J MEDIAN_USED C32
K MODE_USED C33
L RDGS_ALL C34
M RDGS_USED C35
N NUMPTS_CAVE_CONTAM
O NUMPTS_RECYCLED_OXI
P COMMENTS
Q DATALIST1 B8:B21, but I need a comma that separates the numbers
R DATALIST2 C8:C17, but I need a comma that separates the numbers
S DATALIST3
T DATALIST4

Is this type of macro even possible, I just want to automate the process so
I don’t have to cut and copy anymore. This type of VBA is out of my skill
range, I appreciate any help.

 

[dan dungan]

I'm not clear what type of macro you are trying to create.

Do you want to transpose the data from rows to columns?

Is the template spreadsheet the file specification for the file to
import into Oracle?

Please show the code for your macro.

 

[James]

The data comes to me in one workbook with multiple sheets. I have a template
that is specified for my oracle uploading. I would like to see the data come
from the provided data into my template. My template is set-up so that each
sample has its own row. I do not think a transpose will work for this.
Below you will find the code I have. This code is only for locating where
the data is coming from, I do not know how to attack the importing part of
the macro.

Sub Humble_DataImport()
On Error GoTo err_ImportData

Const lngLast As Long = 65536
Dim lngLastRow As Long
Dim i As Long
Dim r As Long
Dim j As Integer
Dim strDataFileName As String
Dim strInitFileName As String
Dim strInitShtName As String
Dim intStartRow As Integer
Dim strLookupShtName As String
Dim bFlag As Boolean
Dim k As Integer
Dim m As Integer
Dim intFirstInputRow As Integer
Dim intLastInputCol As Integer
Application.ScreenUpdating = False

strInitFileName = ActiveWorkbook.Name
strInitShtName = ActiveSheet.Name
intStartRow = 9 'beginning row on lookup sht
intFirstInputRow = 3 'first row on template sheet
intLastInputCol = 27 'last column we're importing on template sheet,
currently T (20)
r = intFirstInputRow
k = 1
m = 1

lngLastRow = Cells(lngLast, 1).End(xlUp).Row
If lngLastRow > intFirstInputRow - 1 Then
Range(Cells(intFirstInputRow, 1).Address & ":" & Cells(lngLastRow,
intLastInputCol).Address).Clear
End If

'obtain and open data file
strDataFileName = Application.GetOpenFilename("Microsoft Excel (*.xls),
*.xls")
bFlag = True
If strDataFileName = False Then
If k <> 2 Then
MsgBox "No file was selected. This procedure will now be
terminated."
GoTo exit_ImportData
End If
End If
If strDataFileName = "" Or Len(strDataFileName) = 0 Then
MsgBox "No file was selected. This procedure will now be
terminated."
GoTo exit_ImportData
End If
bFlag = False

'check to see if file already open
For i = 1 To Workbooks.Count
If Workbooks(i).FullName = strDataFileName Then
Workbooks(i).Activate
strDataFileName = Workbooks(i).Name
m = 2
Exit For
End If
Next i

'don't reopen
If m = 1 Then
Workbooks.Open Filename:=strDataFileName
strDataFileName = ActiveWorkbook.Name
End If
ActiveWorkbook.Sheets("report").Activate
strLookupShtName = ActiveSheet.Name
lngLastRow = Cells(lngLast, 1).End(xlUp).Row

'cycle thru rows and input data
For i = intStartRow To lngLastRow
If Len(Workbooks(strDataFileName).Sheets(strLookupShtName).Cells(i,
2).Value) > 0 Then

 

[dan dungan]

Hi James,

I'm still not clear about your requirements. My questions are below:

------------------------------------------------------------------------------------------

The data comes to me in one workbook with multiple sheets. I have a template
that is specified for my oracle uploading. I would like to see the data come

How often do you receive the workbook?

Do you use the data contained in the multiple sheets?

Does your template have multiple sheets?

Is the template file extension ".xlt"?

Earlier you mentioned that you wanted to automate to stop cutting and
copying. Did you try recording these manual actions in a macro? If so,
what does it look like?

from the provided data into my template. My template is set-up so that each
sample has its own row. I do not think a transpose will work for this.

Does the workbook you receive with multiple sheets contain one sample
on each sheet?

It appears the data in the sample has each sample field on a new row.
Your template appears to put all the fields from the sample record in
one row. That seems like a transposition to me.

Below you will find the code I have. This code is only for locating where
the data is coming from, I do not know how to attack the importing part of
the macro.

Where do you store the macro? In a general module, a sheet module,
this workbook module?

Dan
________________________________________________