How to generate table for the data available in excel?

Help Studio
, ,
1

I want to create a table for whatever data is available in Excel sheet. Data rows count is not same all the time.

Can someone help?

2

@PALKUMARI_PATEL You want to create data table or just table based on excel data. if it data table use the workflow below. If not can you explain the process a bit. Why you want to create a table?

Capture

Example.zip (10.9 KB)

3

Hey @ushu As per my client’s requirement, I have to create a table for whatever data is available on the file. I am getting data from SQL Database.

I have run the macro but I don’t know how to make it dynamic to get the data range.
Sub Macro1()
’
’ Macro1 Macro
’

’
Range(“A1:K5484”).Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range(“$A$1:$K$5484”), , xlNo).Name = _
“Table1”
Range(“Table1[#All]”).Select
End Sub

Or either way you can suggest something so I can try other than macro.

4

Getting below error:

Invoke VBA: Cannot run the macro ‘Macro1’. The macro may not be available in this workbook or all macros may be disabled.

I checked trust center settings and enabled all macros.

5

@PALKUMARI_PATEL Try this open your excel sheet in which you want to run this code.Use shortcut key Alt F11. Then copy this code in the opened module and run the code, see how it goes

Capture
Capture560×524 22.4 KB

6

I don’t understand what is this for ? I want to create a table for available data only .

7

@PALKUMARI_PATEL This is the one way of running the macros. Since you are getting that error I just want to check with the code first. If it works then you can easily resolve the above issue

8

I can run the code.

9

@PALKUMARI_PATEL Is it working as expected?

10

no, I am getting table for blank rows also, I just want to create a table upto where the data rows are. Let’s say I have 100 rows and A to J columns then I am getting table for all rows and A:j Column.

11

@PALKUMARI_PATEL Try to change the range in the macro in the below mentioned places as A1:J100. Check the results

Capture

12

I did A:J and it works but if I have 100 rows then only want to get table for 100 rows. By selecting range A:J it is creating table for 101,102,103… which are blanks.

13

@PALKUMARI_PATEL Did you give A1:J100 in the code

14

No, Because I want dynamic values here. all the time data rows will be different, 100,200,300,400 …

15

@PALKUMARI_PATEL Yes you can pass dynamically. But, first test with the given range if it is working as expected then you can check how to pass those values dynamically. If the code didn’t work as expected then its doesn’t make sense of looking how to pass the values dynamically

Hope this helps !!

16

yes, @ushu that is working as expected. just need to pass dynamic values now.

17

@PALKUMARI_PATEL Can you share this code in a notepad

18

New Text Document.txt (245 Bytes)

19

@PALKUMARI_PATEL You can use the read range to get the rows count and this can pass dynamically to the Invoke VBA

Capture
Capture989×510 21.9 KB

Please find attached workflow, use the updated notepad

Example.zip (2.7 KB)

1 Like
20

Hey @ushu

It’s working as expected now. Thank you so much for sharing. Really appreciate it.