Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help...I need a lot
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 dont have to cut and copy anymore. This type of VBA is out of my skill range, I appreciate any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help...I need a lot
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help...I need a lot
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(strLookupSht Name).Cells(i, 2).Value) 0 Then "dan dungan" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help...I need a lot
Hi James,
I'm still not clear about your requirements. My questions are below: ------------------------------------------------------------------------------------------ On Mar 24, 4:51 am, James wrote: 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 ________________________________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|